Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mocity
 
Posts: n/a
Default Line or bar graphs for tracking stocks profit and loss.

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?
  #2   Report Post  
John Mansfield
 
Posts: n/a
Default

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.

----
Regards,
John Mansfield
http://www.pdbook.com



"Mocity" wrote:

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"