Graphing a Time Series of Several Options

[carl]

I am having a difficult time getting this data to plot correctly. Is possible
to plot the data ??

Thank you in advance.

TradeSymbol TradePrice ExecutionDateTime
TQAA06C10.00 6.9 38525.6115393518
TQAH05C20.00 0.1 38525.6113310185
TQAH05C20.00 0.1 38525.6113310185
TQAK05C17.50 5.2 38525.6111226852
TQAK05C17.50 5.2 38525.6111226852
TQAK05C17.50 5.1 38525.6111226852
TQAK05C17.50 5.1 38525.6111226852
TQAK05C17.50 5.1 38525.6111111111
TQAK05C17.50 5.1 38525.6111111111
VCYA07C15.00 3 38525.611099537
VCYA07C15.00 3 38525.611099537
TQAK05C17.50 5 38525.611087963
TQAK05C17.50 5 38525.611087963
TQAH05C15.00 2.25 38525.6110532407
TQAH05C15.00 2.25 38525.6110532407
TQAK05C17.50 2.1 38525.6110416667
TQAK05C17.50 2.1 38525.6110416667
VCYA07C17.50 1.8 38525.6110416667
VCYA07C17.50 1.8 38525.6110416667
TQAK05C15.00 4.2 38525.6108680556
TQAK05C15.00 4.2 38525.6108680556
TQAK05C17.50 2.1 38525.6108680556
TQAK05C17.50 2.1 38525.6108680556
TQAK05C15.00 3.5 38525.6107060185
TQAK05C15.00 3.5 38525.6107060185
TQAK05C17.50 1.6 38525.6106944444
TQAK05C17.50 1.6 38525.6106944444
TQAK05C17.50 1.6 38525.6106944444
TQAK05C17.50 1.6 38525.6106944444
TQAK05C15.00 3.5 38525.6106944444
TQAK05C15.00 3.5 38525.6106944444
TQAG05C15.00 2.7 38525.6100231481
TQAG05C15.00 2.7 38525.6100231481
TQAG05C15.00 2.7 38525.6100231481
TQAG05C15.00 2.7 38525.6100231481
TQAG05C15.00 2.7 38525.6100231481
TQAG05C15.00 2.7 38525.6100231481
TQAK05C17.50 1.6 38525.6097337963
TQAK05C17.50 1.6 38525.6097337963
TQAK05C17.50 2.1 38525.6096875
TQAK05C17.50 2.1 38525.6096875
TQAK05C15.00 3.5 38525.6096875
TQAK05C15.00 3.5 38525.6096875
TQAK05C15.00 3.4 38525.6096759259
TQAK05C15.00 3.4 38525.6096759259
TQAK05C15.00 3.4 38525.6096643519
TQAK05C15.00 3.4 38525.6096643519
TQAK05C17.50 1.55 38525.6096643519
TQAK05C17.50 1.55 38525.6096643519
TQAK05C15.00 5.8 38525.6094907407
TQAK05C15.00 5.8 38525.6094907407
TQAK05C17.50 2.15 38525.6093865741
TQAK05C17.50 2.15 38525.6093865741
TQAK05C17.50 5.6 38525.6091782407
TQAK05C17.50 5.6 38525.6091782407
TQAK05C15.00 6 38525.6091782407
TQAK05C15.00 6 38525.6091782407
TQAK05C17.50 5.5 38525.6091666667
TQAK05C17.50 5.5 38525.6091666667
TQAK05C15.00 5.9 38525.6091550926
TQAK05C15.00 5.9 38525.6091550926
TQAK05C17.50 5.4 38525.6091550926
TQAK05C17.50 5.4 38525.6091550926
TQAK05C15.00 5.8 38525.6091435185
TQAK05C15.00 5.8 38525.6091435185
TQAK05C17.50 5.3 38525.6091319444
TQAK05C17.50 5.3 38525.6091319444
TQAK05C17.50 5.2 38525.6090162037
TQAK05C17.50 5.2 38525.6090162037
TQAK05C15.00 5.7 38525.6090046296
TQAK05C15.00 5.7 38525.6090046296
TQAK05C17.50 5.1 38525.6089930556
TQAK05C17.50 5.1 38525.6089930556
TQAK05C15.00 5.6 38525.6089930556
TQAK05C15.00 5.6 38525.6089930556
TQAK05C17.50 5 38525.6089814815
TQAK05C17.50 5 38525.6089814815
TQAK05C15.00 5.5 38525.6089814815
TQAK05C15.00 5.5 38525.6089814815
TQAG05C15.00 2.7 38525.6089699074
TQAG05C15.00 2.7 38525.6089699074
TQAG05C15.00 2.7 38525.608912037
TQAG05C15.00 2.7 38525.608912037
TQAG05C15.00 2.7 38525.608912037
TQAG05C15.00 2.7 38525.608912037
TQAM06P15.00 0.95 38525.6088888889
TQAM06P15.00 0.95 38525.6088888889
TQAM06P15.00 0.95 38525.6086458333
TQAM06P15.00 0.95 38525.6086458333
TQAG05C15.00 2.45 38525.6085185185
TQAG05C15.00 2.45 38525.6085185185
TQAG05C15.00 2.5 38525.6084837963
TQAG05C15.00 2.5 38525.6084837963
TQAG05C15.00 2.5 38525.6084027778
TQAG05C15.00 2.5 38525.6084027778
TQAM06P15.00 0.95 38525.6083101852
TQAM06P15.00 0.95 38525.6083101852
TQAG05C15.00 2.25 38525.6079861111
TQAG05C15.00 2.25 38525.6079861111
TQAK05C17.50 11 38525.6072337963
TQAK05C17.50 11 38525.6072337963
TQAK05C15.00 2.75 38525.6072222222
TQAK05C15.00 2.75 38525.6072222222
TQAA06C15.00 2.45 38525.6072106482
TQAA06C15.00 2.45 38525.6072106482
TQAK05C17.50 10.9 38525.6072106482
TQAK05C17.50 10.9 38525.6072106482
TQAK05C15.00 2.7 38525.6072106482
TQAK05C15.00 2.7 38525.6072106482
TQAK05C17.50 10.8 38525.6071990741
TQAK05C17.50 10.8 38525.6071990741
TQAA06C15.00 2.1 38525.6071180556
TQAA06C15.00 2.1 38525.6071180556
TQAA06C10.00 6.9 38525.6070717593
TQAA06C10.00 6.9 38525.6070717593
TQAH05C15.00 1.25 38525.6070601852
TQAH05C15.00 1.25 38525.6070601852
TQAH05C20.00 0.15 38525.6070486111
TQAH05C20.00 0.15 38525.6070486111
TQAH05C12.50 4.6 38525.6070486111
TQAH05C12.50 4.6 38525.6070486111
TQAK05C17.50 10.7 38525.6070486111
TQAK05C17.50 10.7 38525.6070486111
TQAA06C7.50 9.5 38525.6070486111
TQAA06C7.50 9.5 38525.6070486111
TQAH05C15.00 2.3 38525.6070486111
TQAH05C15.00 2.3 38525.6070486111
VCYA07C17.50 1.8 38525.6070486111
VCYA07C17.50 1.8 38525.6070486111
VCYA07C17.50 1.8 38525.6070486111
VCYA07C17.50 1.8 38525.6070486111
TQAA06C15.00 2.5 38525.6070486111
TQAA06C15.00 2.5 38525.6070486111
TQAK05C17.50 1.8 38525.605775463
TQAK05C17.50 1.8 38525.605775463
TQAA06C17.50 1.6 38525.6050810185
TQAA06C17.50 1.6 38525.6050810185
TQAG05C15.00 1.15 38525.6049652778
TQAG05C15.00 1.15 38525.6049652778
TQAG05C15.00 1 38525.6049074074
TQAG05C15.00 1 38525.6049074074
TQAG05C15.00 1 38525.6049074074
TQAG05C15.00 1 38525.6049074074
TQAG05C17.50 0.3 38525.6048611111
TQAG05C17.50 0.3 38525.6048611111
TQAG05C17.50 0.3 38525.604849537
TQAG05C17.50 0.3 38525.604849537
TQAG05C17.50 0.2 38525.604849537
TQAG05C17.50 0.2 38525.604849537
TQAG05C17.50 0.2 38525.604849537
TQAG05C17.50 0.2 38525.604849537
TQAG05C17.50 0.15 38525.604849537
TQAG05C17.50 0.15 38525.604849537
TQAG05C17.50 0.15 38525.604849537
TQAG05C17.50 0.15 38525.604849537
TQAK05C15.00 9.2 38525.6047685185
TQAK05C15.00 9.2 38525.6047685185
TQAA06C17.50 1.7 38525.6047569444
TQAA06C17.50 1.7 38525.6047569444
TQAA06C17.50 1.7 38525.6047569444
TQAA06C17.50 1.7 38525.6047569444
TQAG05C15.00 1 38525.6047453704
TQAG05C15.00 1 38525.6047453704
TQAG05C15.00 0.75 38525.6047453704
TQAG05C15.00 0.75 38525.6047453704
TQAK05C17.50 10.7 38525.6047453704
TQAK05C17.50 10.7 38525.6047453704

 

[Jon Peltier]

Carl -

You don't describe how you want it plotted, but I'll make a guess that
you want time as your X axis, and a separate series (set of markers) for
each trade symbol.

Make a pivot table of your data. Put ExecutionDateTime in the row area,
TradeSymbol in the column area, and TradePrice in the data area. If
there are multiple records for a given TradeSymbol and
ExecutionDateTime, you need to double click on the Sum of TradePrice
field button (top left of the pivot table) and select Average from the
Summarize By list.

The easiest way to make a scatter chart from a pivot table is to use a
copy of the values from the pivot table. Copy the pivot table (except
for any grand total row or column), select a cell in a blank area of the
sheet, and from the Edit menu, use Paste Special - Values to paste these
values into the new area. Delete the field names (ExecutionDateTime,
TradeSymbol, ExecutionDateTime) so you have the actual TradeSymbol
labels in the top row of your new table, ExecutionDateTime values in the
first column, and the top left cell is blank. Select the
ExecutionDateTime column, select Cells from the Format manu, and on the
Number tab, select a Time or Date format. Now select the range and start
the chart wizard. Choose an XY Scatter type in step 1, and in step 2,
make sure the Series in Columns option is selected. Then complete the
wizard, and format your chart as required.

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