| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Graphing A Time Series of Multiple Stock Prices | Excel Worksheet Functions | |||
| Can series tool change formulas over time when used to copy them | Excel Worksheet Functions | |||
| prehistoric time series | Excel Worksheet Functions | |||
| How to exclude weekends/holidays from plotted time series | Charts and Charting in Excel | |||
| graphing two series on x axis | Charts and Charting in Excel | |||
Linear Mode
