Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
carl
 
Posts: n/a
Default Graphing A Time Series OF Stocks

My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.
  #2   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

try using series. series 1 for EFG and sereis 2 for ABC



carl wrote in message
...
My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.



  #3   Report Post  
Tushar Mehta
 
Posts: n/a
Default

Your data are perfectly organized for a PivotTable (and PivotChart).
Select Data | PivotTable and PivotChart report... (the name has changed
with different versions of XL).

Put the ExecutionDateTime as the row field, the Stock as the column
field and the TradePrice as the data field. By default, XL will use
'Sum of TradePrice' as the data field, but that is OK -- as long as you
do not have multiple trades for the same stock at the same time.

Of course, once you create your basic table and chart, you can
experiment with the layout to see if something else is more appealing
to you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.

  #4   Report Post  
carl
 
Posts: n/a
Default

thanks. is there a way to handle the case where I have multiple prices at the
same time ? This occurs frequently.

"Tushar Mehta" wrote:

Your data are perfectly organized for a PivotTable (and PivotChart).
Select Data | PivotTable and PivotChart report... (the name has changed
with different versions of XL).

Put the ExecutionDateTime as the row field, the Stock as the column
field and the TradePrice as the data field. By default, XL will use
'Sum of TradePrice' as the data field, but that is OK -- as long as you
do not have multiple trades for the same stock at the same time.

Of course, once you create your basic table and chart, you can
experiment with the layout to see if something else is more appealing
to you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.


  #5   Report Post  
Tushar Mehta
 
Posts: n/a
Default

You have multiple prices for the same stock at the same time?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
thanks. is there a way to handle the case where I have multiple prices at the
same time ? This occurs frequently.

"Tushar Mehta" wrote:

Your data are perfectly organized for a PivotTable (and PivotChart).
Select Data | PivotTable and PivotChart report... (the name has changed
with different versions of XL).

Put the ExecutionDateTime as the row field, the Stock as the column
field and the TradePrice as the data field. By default, XL will use
'Sum of TradePrice' as the data field, but that is OK -- as long as you
do not have multiple trades for the same stock at the same time.

Of course, once you create your basic table and chart, you can
experiment with the layout to see if something else is more appealing
to you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.





  #6   Report Post  
carl
 
Posts: n/a
Default

yes. i run an exchange. a given order can be split into multiple trades all
taking place at the same time and same price.

"Tushar Mehta" wrote:

You have multiple prices for the same stock at the same time?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
thanks. is there a way to handle the case where I have multiple prices at the
same time ? This occurs frequently.

"Tushar Mehta" wrote:

Your data are perfectly organized for a PivotTable (and PivotChart).
Select Data | PivotTable and PivotChart report... (the name has changed
with different versions of XL).

Put the ExecutionDateTime as the row field, the Stock as the column
field and the TradePrice as the data field. By default, XL will use
'Sum of TradePrice' as the data field, but that is OK -- as long as you
do not have multiple trades for the same stock at the same time.

Of course, once you create your basic table and chart, you can
experiment with the layout to see if something else is more appealing
to you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.




  #7   Report Post  
Jon Peltier
 
Posts: n/a
Default

Use the Average of Trade Price option, not the default Sum (why Sum is
default, I'll never figure out). Then drag another copy of the Trade
Price field to the Row Area. Here's how the pivot table looks:

Average of TradePrice Stock
ExecutionDateTime TradePrice ABC EFG
2:30:51 PM 1.7 1.7
2:31:16 PM 1.6 1.6
2:31:17 PM 1.5 1.5
2:40:00 PM 5.1 5.1
2:40:01 PM 5.1 5.1
5.3 5.3
2:40:02 PM 5.5 5.5
2:40:04 PM 5.2 5.2

At 2:40:01 you can see two different prices in the data area.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______



carl wrote:

yes. i run an exchange. a given order can be split into multiple trades all
taking place at the same time and same price.

"Tushar Mehta" wrote:


You have multiple prices for the same stock at the same time?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

thanks. is there a way to handle the case where I have multiple prices at the
same time ? This occurs frequently.

"Tushar Mehta" wrote:


Your data are perfectly organized for a PivotTable (and PivotChart).
Select Data | PivotTable and PivotChart report... (the name has changed
with different versions of XL).

Put the ExecutionDateTime as the row field, the Stock as the column
field and the TradePrice as the data field. By default, XL will use
'Sum of TradePrice' as the data field, but that is OK -- as long as you
do not have multiple trades for the same stock at the same time.

Of course, once you create your basic table and chart, you can
experiment with the layout to see if something else is more appealing
to you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.


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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Graphing A Time Series of Multiple Stock Prices carl Excel Worksheet Functions 1 June 25th 05 11:15 AM
Can series tool change formulas over time when used to copy them compu_trainer Excel Worksheet Functions 3 May 26th 05 07:55 PM
prehistoric time series Folke Günther Excel Worksheet Functions 3 April 21st 05 02:18 PM
How to exclude weekends/holidays from plotted time series Debbie424242 Charts and Charting in Excel 3 January 19th 05 08:00 PM
graphing two series on x axis Ladislav Ligart Charts and Charting in Excel 2 December 6th 04 07:50 PM


All times are GMT +1. The time now is 03:40 AM.

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"