Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Businesses and individuals invest in the stock market, whether trying to invest for retirement or to make a profit from cash on hand. Excel is a great application for tracking stock prices. So in this movie, I will show you how to format your data so you can create stock charts. Excel let's you create a number of stock charts. The limitation is that your data must be set up in exactly the right format for each of them. Let me show you what I mean. The data that I have here in this worksheet has five columns. I have the date that the measurements were taken. Then I have Open, High, Low, and Close.
So as you might suspect, in Excel you can create what's called an Open-High-Low-Close stock chart. So with a cell selected in my data set, I will go up to Charts, and then there is no stock chart here in the main body of the Ribbon, so I need to click Other, and then here at the top, you see that I have a series of stock charts of High-Low-Close, Open- High-Low-Close, Volume-High-Low-Close, and Volume-Open-High-Low.
And what these stock chart names tell you is exactly the data that you need to have. So in my case, I have Open, High, Low, and Close, plus date, which is always included in every stock chart. So that means I can create an Open-High-Low-Close stock chart. To do that, I click that button and Excel creates my chart. Now this chart is a little deceiving. Now it's true that relative to its price, this stock isn't varying very much, but it also makes the chart a little bit harder to read.
So I am going to do two things to make it easier to read. And again, we are getting a little bit ahead of ourselves, but the formatting changes I am going to show you are inextricably linked with stock charts. So it makes sense to them here. First, I am going to make sure the chart is selected. It is; I can tell it from its outline. And then the Charts tab of the Ribbon, I will click the Chart Styles gallery's down arrow, and I will select a format that I think is a little bit easier to read. I will select the red and white. Now the elements in the interior of the graph have a little bit more contrast than they did before.
Now, the elements of the charts, the candlesticks as they are called here in the middle, are still little cramped. So what I am going to do is I am going to change the values that are displayed on the vertical axis. To do that, I will click Chart Layout, click Axes, point to Vertical Axis, and then click Axis Options. When I do, the Scale page of the Format Axis dialog appears. I will keep my maximum value of 400, but what I want to do is get my minimum value up to 250 so that the candlesticks on the inside of the chart are a little bit larger.
So I will click here, type 25, to make the value of 250 and then with that in place, I will click OK, and now I have my chart. And the candlesticks are a little bit larger, and they are easier to interpret. So how do we interpret the chart? Well in this version of the chart, any day where the value of the chart went up, the body of the candle is white. On any day that the value of the stock went down, the body of the candle is red, and here is how you read each individual day.
For September 13, the value started here went up to here at the end of the day, and at its highest point it was here. This was an Open-High-Low-Close stock chart. So that means you are getting that information. Here is the Open and also the Low, here is the High, and here is the Close. Same thing here. Here is the Open; here is the High, here is the Close. The last bit in the actual candle part of the candlestick, the Up bar as it is called, and then this is also the Low.
For a down day, it started here, and you can see that is called a Down bar. It started her, went all the way down as far as here, but then it closed here at this value. Stock charts help you track the stocks that interest you. If you want more information on how to use stock charts, you should go to your bookstore, and look for books on candlestick charts, which is another name for stock charts.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 81376 Viewers
80 Video lessons · 133269 Viewers
52 Video lessons · 66631 Viewers
59 Video lessons · 52447 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.