Consolidate my data and plot on a dual axis chart
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 |
Consolidate my data and plot on a dual axis chart
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bruce" wrote in message ... 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 |
Consolidate my data and plot on a dual axis chart
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" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bruce" wrote in message ... 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 |
Consolidate my data and plot on a dual axis chart
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bruce" wrote in message ... 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" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bruce" wrote in message ... 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 |
All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com