How to add DowJones Line Chart on to Yahoo Stock Chart

Greetings,
1- Open enclosed file.
2- Run Macro1.

Please Login or Register  to view this content.

After running Macro1, why I cant see DOW JONES LINE CHART on the chart.
Red lines need to be corrected.

Thanks in advance.

Notes;
1- I want this because I want to compare YAHOO and DOW JONES.
2- Be careful that I am using xlStockVOHLC chart not xlStockOHLC chart.

Unless I am misunderstanding the problem, there is nothing wrong with the syntax or application of those lines of code, and the Dow Jones series appears to be there exactly like your code told it to. The "problem" is that the values for the Dow Jones series are about 8E4, where the scale of the chart goes from 0 to 2E8. So, the Dow Jones columns extend roughly 1/1000 the length of the axis -- probably not something immediately visible at the scale of most computer monitors.

I'm not sure what to recommend. The problem is not in your code or otherwise in your application of the code. The problem is the common (whether in Excel, VBA, or whatever you use for charting/graphing) "how do I create a meaningful chart when the values for one data series are so much larger than the other data series?" As you are aware, Jon Peltier and others like to recommend a panel chart to deal with this issue, and that may be a useful approach, depending on exactly what comparison you want your readers to be able to see.

Hi MrShorty,
First of all thanks your answer.

Originally Posted by MrShorty

The "problem" is that the values for the Dow Jones series are about 8E4

I dont agree with you if the problem is regarding to scale. Because when I change the data like another scale and then run Macro1, there will be no change.

Originally Posted by MrShorty

As you are aware, Jon Peltier and others like to recommend a panel chart to deal with this issue

I dont prefer panel data chart because of some reasons...But thank you...

I dont agree with you if the problem is regarding to scale. Because when I change the data like another scale and then run Macro1, there will be no change.

Can you supply a sample that illustrates this problem? The sample file in the OP seems to work just fine (for me anyway), except for the problem of scale. One quick test (though I would not recommend this as the final solution) is to format the primary y axis as a logarithmic scale. When I did that on the sample file you provided, the Dow Jones series immediately became visible, so the code did add the data series to the chart. Perhaps there is something deeper in the problem and a better sample file that illustrates the problem will help.

I dont prefer panel data chart because of some reasons...But thank you...

I don't believe that a panel chart is the answer in all cases, but it can be very useful in cases where the different y series are composed of vastly different values. Perhaps if you explained why you don't want to use a panel chart -- what you feel it fails to show -- that will help us better understand what it is you are trying to show with these charts. Once we better understand what you are trying to show with these charts, we may be able to suggest a better chart type.

Originally Posted by MrShorty

Can you supply a sample that illustrates this problem?

Dear MrShorty,
1- Open the file which I sent in the first thread.
2- In order to make equal scales, copy second row and paste on third row.
3- Run Macro1.
Then please tell me if chart is meaningful or not.
Best regards.

It appears to me to be essentially the same chart. The only thing copying row 2 to row 3 does is overwrite the $80k dollar values that are originally in the Dow Jones series with the 8 to 9 values from row 2. These values are smaller still compared to the overall scale of the primary value axis, and so they are even harder to see. As with the previous instruction, I can readily see the Dow Jones series by formatting the primary axis as a logarithmic axis, so I can see that it is still there.

As far as any meaning that I see in the chart. Speaking as a non-financial person, all I get out of this chart is that the Dow Jones Close values are dramatically smaller than the Volume values. I can see the High/low/close box and whisker plots, but I cannot immediately tell what these are trying to tell me (probably because, again, I am not in financials, so I would not immediately know what this is supposed to show me).

Not sure if I am being helpful or not, but there it is.

Originally Posted by MrShorty

One quick test (though I would not recommend this as the final solution) is to format the primary y axis as a logarithmic scale. When I did that on the sample file you provided, the Dow Jones series immediately became visible...

I am so sorry that I skipped your recommendation above... Can you tell me how do you make logarithmic scale?

Step 3d here (https://support.office.com/en-US/art...rs=en-US&ad=US ) when using Excel's interface.

I am not familiar with the VBA code for it, but I expect that if you search through the availabe "axis object" properties, there will be a "scaletype" property or "logscale" toggle that would be used to toggle between linear and logarithmic scales: https://msdn.microsoft.com/en-us/lib.../dn254107.aspx

I am using excel 2013...So, I am not successful how to change the primary y axis as a logarithmic scale.
Could you please upload a picture, how to do and how chart is looking...

or,

Could you please upload an excel file with logarithmic scaled chart....

I don't think it is that different in 2013. This help file shows the axis formatting dialog in 2013 https://support.office.com/en-US/Art...a-cc7e4584456a Right above the "tick marks" option pull down are two check boxes, one of which is for the logarithmic axis option.

Here is your file, after running macro1 and formatting the primary value axis as logarithmic.

As a scientist, I am comfortable with logarithmic axes. I understand how a log scale exaggerates small values and compresses large values. Most people do not intuitively understand a log scale axis, so a log scale is not a good idea. As a debugging step, it may be useful for you to see that the chart is adding the Dow Jones series. I don't expect you will want to use this as your final solution.

Do you know what is LogBase = 10 ?

Please Login or Register  to view this content.

Following picture shows before Logarithmic scale.
http://i.cubeupload.com/gcwqdt.jpg
Following picture shows after Logarithmic scale.
http://i.cubeupload.com/Zvkp9A.jpg
Please compare Blue Bars...
I think logarithmic scale is not useful....

A couple of quick algebra reviews to remember the basic properties of exponents and logarithms.
http://www.purplemath.com/modules/logs.htm
http://www.purplemath.com/modules/exponent.htm

Remember that, in an exponential expression 10^3, the 3 is the exponent and the 10 is the base. When we make a logarithmic axis, the base tells us what number to use in the base for each gridline.

Base 10 gives us gridlines at 10^0=1, 10^1=10, 10^2=100, 10^3=1000 and so on.
Base 2 gives us gridlines at 2^0=1, 2^1=2, 2^2=4, 2^3=8 and so on.

Because our number system is decimal, a base 10 axis tends to make a lot of sense.

Does making that a logarithmic axis help you see that the Dow Jones series is being added correctly to the chart?

I think logarithmic scale is not usefull....

Most would agree with you -- especially for charts aimed at lay people. As I noted, in this case, it is useful as a debugging tool because you can see that the series is there.

So, I guess the next question is -- what are you trying to show with this chart so we can figure out what to do with it to make something more useful?

Originally Posted by MrShorty

So, I guess the next question is -- what are you trying to show with this chart so we can figure out what to do with it to make something more useful?

I am trying to compare and see Yahoo stock prices and Dow Jones Index on the chart.
Because I need to see Yahoo stock prices relatively Dow Jones Index...

I am trying to compare and see Yahoo stock prices and Dow Jones Index

As I noted earlier, this is a field that I am not familiar with. In your spreadsheet, I see data that is labeled "VOLUME". Is the same thing as "price" or is it something different? As an outsider looking in, one of my first concerns is if we are comparing the same things (dollars to dollars).

If these are the correct values, then there is no getting around the fact that the yahoo volumes are ~1000 times larger than the Dow Jones numbers. My first suggestion might be a search for how other financial analysts perform this kind of comparison. This seems like it should be a common enough analysis, with the same kind of problems that you are coming across. Take this question to a group of financial/stock market analysts and see how they deal with the issue. Bring back a few sample charts with an explanation of what they did to get those charts, and we will be able to help you program Excel/VBA to obtain similar results.

You have said that you don't want to use a panel chart. I could still see a panel chart perhaps being useful here. One panel to show the yahoo volumes dwarfing the Dow Jones values, and another chart to show the Dow Jones values. Or perhaps a third quantity like Yahoo volume/Dow Jones (you would need to add this calculation in another row). Then you can have a panel that shows one of the quantities, and another panel to display this ratio.

The hard part of some charting problems is not about Excel/VBA, but simply trying to figure out what to show.

"Volume" means price*quantity.

Do you make a panel chart with vba?

Do you make a panel chart with vba?

I'm sure it can be done. I personally do it without VBA, the same way that Jon Peltier describes it. I see no reason why someone with the desire and expertise could not use VBA to perform the same steps/operations to create a panel chart.

on edit:

"Volume" means price*quantity.

Is that the same definition for the Dow Jones values? I would have thought the price*quantity for the Dow Jones stocks would be much higher than $100k

Originally Posted by MrShorty

I'm sure it can be done.

So, lets start making panel chart

Originally Posted by MrShorty

I would have thought the price*quantity for the Dow Jones stocks would be much higher than $100k

Dow Jones is just for easy understanding...The data is coming from Turkish Stock Index

Please;
1- Open enclosed file.
2- Run Macro1.(to make Chart1)
3- Run Macro2.(to make Chart2)

So, lets make a panel chart with Chart1 and Chart2 via vba code.

Dow Jones is just for easy understanding...The data is coming from Turkish Stock Index

Before we go to the trouble, are you sure you are going to run into the same problem with the actual volumes=price*quantity that you want to add to this chart? The reason for using a panel chart here is because your example values were so different in magnitude (factor of 1000) that it was difficult to compare those values on the same chart. If the actual values you will be comparing are similar in magnitude (maybe factor of 10), then a panel chart will likely be more work that it is worth, because the results can be compared on the same chart.

Just something to think about before we go into this.

Lets just start making a panel chart as I have explained in the #20. thread.
At least we will see if we can make a panel chart via vba code...

As you wish, though I don't think I, personally, will be much help. As I noted above, I never use VBA to create charts, so I am not going to be immediately conversant with the details. Probably the only help I will be able to give is to point you to the Excel object model pages in the help files, which I'm sure you could do just as effectively.

Do you have any specific questions about putting together a panel chart? Are you familiar with Jon Peltier's tutorials for this?

I have no idea how to make panel chart.
If you never use VBA to create charts, please make a panel chart without VBA...
Thanks in advance.

I assumed you had studied Jon's example: http://peltiertech.com/Excel/ChartsH...venScales.html If not, I would suggest we start with Jon's tutorial. He walks you through the process step by step. Once you understand the procedure he describes, we should be able to readily adapt it to the chart you are trying to create here.