Line or bar graphs for tracking stocks profit and loss.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to show profit and loss over a 12 week period with stocks. A line
graph is the best way but bar graphs are useful as well. However, I m not
able to find either in excel. Does anyone out there have any suggestions?
 
Mocity,

Assume the data below reflects the one stock that you want to track. Set up
the weekly profit and loss in the “Prof / Loss†column. Set up the
cumulative profit / loss in the “Cumulative†column.

Prof/Loss Cumulative
Week 1 55 55
Week 2 47 102
Week 3 -32 70
Week 4 34 104
Week 5 -14 90
Week 6 25 115
Week 7 36 151
Week 8 14 165
Week 9 18 183
Week 10 21 204
Week 11 -15 189
Week12 18 207

Assuming the “Prof/Loss†title is in cell B2 and the “Cumulative†title is
in cell C2, the formulas for the above look like this:

Prof/Loss Cumulative
Week 1 55 =B2
Week 2 47 =SUM($B$2:B3)
Week 3 -32 =SUM($B$2:B4)
Week 4 34 =SUM($B$2:B5)
Week 5 -14 =SUM($B$2:B6)
Week 6 25 =SUM($B$2:B7)
Week 7 36 =SUM($B$2:B8)
Week 8 14 =SUM($B$2:B9)
Week 9 18 =SUM($B$2:B10)
Week 10 21 =SUM($B$2:B11)
Week 11 -15 =SUM($B$2:B12)
Week12 18 =SUM($B$2:B13)

To set up the chart, put your curser in cell A1. Go to the Chart Wizard and
in Step 1 choose the Custom Types Tab -> Line-Column on Two Axis Chart. Go
through the rest of the prompts by hitting the “Next†button (you can choose
headings, etc. here). Double-click on the dates and in the Format Axis
Dialog box go to Patterns -> Tick Mark Labels = Low. At this point you will
have a chart that tracks weekly profit and loss on one axis and the
cumulative profit and loss on the second axis.
 
Back
Top