Consolidate my data and plot on a dual axis chart

[Bruce]

Hi,

I have some stock market data that I is downloaded and in a fixed format but
am having trouble plotting in the dersired manner.

The aim is to plot price on the x axis and volume on the y axis, the twist
is there is a set of data for the bid and a set for the ask price.

The raw data
a) The bid price is in column D
b) The bid volume is in column C
c) The ask price is in column G
d) The ask volume is in column F

The chart
a) The values in the bid price and ask price (col D and G) need to be
combined to make a series for the x axis of the chart as the range overlaps.
b) The bid volume and ask volume are plotted as 2 dependant variables on the
y axis on the same primary axis.

I think the answer is to consolidate the data from 4 columns into 3 where
the 2 price fields are combined. I then know how to plot this as a chart. I
have tried with a pivot table but cant get it right.

Any tips appreciated.

Bassmann

 

[Jon Peltier]

Why do you want to combine the two price fields?

Is the data daily or hourly? In either case, if the dates/times for bid and
ask are not the same you will not get what you expect in a line chart. If
you use an XY chart, both data sets can use their own independent X values.
In a line chart, all series use the same X values as the first series.

If you need a line chart (the only reason is the nice X axis date
formatting, because an XY and Line chart offer the same series formatting
options), you can get around this problem by using all of the X values in
both series, putting blanks in the Y data for one series where only the
other series has a value.

- Jon

 

[Bruce]

Hi Jon,

I think you have misunderstood my request. I wish to plot volume against
price. There is no independant variable i.e. time, as the data is market
depth and not a time series.

My price data is in 2 columns but somehow I need to combine to make 1 series
and plot on the x axis. Then the 2 column series can be plotted on y.

Regards,

Bassmann

 

[Jon Peltier]

Sorry, it's just too deeply ingrained that stock data is plotted against
time.

I still don't know why the data must be combined. Don't you want to
distinguish between bid and ask? Make an XY chart with one set of data, copy
the other set (X and Y values), select the chart, and use Paste Special to
add this data as a new series.

- Jon