In this tutorial, we will see how to create a Stock Chart in Excel.
You’ll learn how to use various types of charts, including the classic Japanese Candlestick Chart.
Turn your Excel into a technical analysis platform, discover all our free tutorials available at this link.
The main financial charts are line, area, stock, and candle charts.
To represent our stock, Excel will use the classic Cartesian chart with its two X and Y axes.
The X-axis will be used for the time variable, so in the case of a daily graph, the date of the day to which the Yahoo Finance OHLC data corresponds.
The date will be increasing from right to left.
The Y will instead report the value of the prices of our financial instrument. The price will be rising from the bottom up.
Excel Stock Chart: Line Graph
What is a line chart
The first chart and also the easiest to represent is the line chart. This graph is straightforward because it uses only one datum, which will usually be the closing price.
This graph is simple to read and can hardly be misinterpreted.
As we have said, the closing price is generally used, but on Excel, we could also use either the opening price or even build our data.
For example, we could calculate the average price using a simple formula that adds the maximum and minimum and divides it by 2 to obtain the average cost to be plotted on the line.
Consider that this simple calculation is not allowed on many platforms.
You could build such a chart very quickly on TradeStation or MultiCharts, but it would be problematic on almost all the others.
Let’s take a quick look at an example; many readers of our site have asked us where to look at the Indian actions of Nifty 50.
We have created a small tutorial that speaks specifically about Indian market shares; you can find it at this link.
Take as an example one of the most outstanding stocks on the Indian market and the Nifty 50, ICICIBANK.
ICICIBANK is an Indian multinational banking and financial services company based in Mumbai.
You will hardly find this title on your trading or analytics platforms, and so if you want to analyze it, you will need to do so on Excel.
On Yahoo Finance, we extract all data relating to ICICIBANK through this tutorial:
How to draw a line chart
To draw a line chart, all we need is the date and close column.
Select the entire date column and then hold down the CTRL key and select the whole close price column.
Then we click on insert and choose the line graph.
There’s something weird about the chart. There’s a spike heading toward zero.
Unfortunately, when we go to take time-series on foreign market stocks like the Indian one, it is effortless to find errors in the data.
We certainly do not know if there are small price errors, but we can detect large holes in the graph, as in this case.
As you can see on November 27, 2019, the data reported is “null,” so Yahoo Finance skipped a trading day.
Now what? In this simple case, delete the entire line to solve the problem.
Indeed, if something particularly important had happened that day, we would lose it in our analysis. In 99% of cases, a daily report with some holes in the time series should not lead to significant errors of interpretation.
Once the line has been deleted, the chart is displayed correctly.
As you can see, Excel has correctly plotted the dates on the X-axis and the closing price values on the Y-axis, and the Stock Chart is ready.
To arrange the line graph, we can right-click on the vertical axis of the values that will be highlighted and then click on the Format Axis.
Let’s start by editing the Maximum and Minimum to display the graph better.
As you can see, the prices are all in a range between 300 and 600 and the market seems to be in an absurd trading range. It’s just an optical effect, by removing empty chart parts such as the 0 to 300 region, the chart takes on a whole different look.
The Bar Chart
The stock chart shows the date on the X-axis, while on the Y-axis, bars are drawn starting from the minimum price until reaching the maximum price. These bars also draw closing and opening prices.
The Bar Chart, one of the most used in Technical Analysis, can represent every price movement through a vertical bar on which the four most important price levels are shown at the same time:
- Open
- Maximum
- Minimum
- Close
The small line on the left of the vertical bar represents the opening price, while the line on the right highlights the closing price.
The total excursion, i.e., the maximum and minimum marked, are represented by the two extreme levels of the bar itself.
This chart was widely used in the past, today it has been extensively replaced by Japanese candle charts but is still widely used.
When there is a need to keep the analysis space cleaner and more empty, we can consider this type of chart.
It is handy for viewing backtests with inputs and outputs.
It is also advantageous when we need to represent extensive periods because it can be significantly reduced without creating problems.
When you zoom out on a candle chart, sometimes you can’t read very well.
Excel Japanese Candlestick Chart
Graphically depicting the trend of the markets through the use of Japanese candles has several advantages, even for beginners.
An immediate and overview of the market trend, as the market imbalance between purchases and sales, is immediately perceived.
The recording of the minimum and maximum points touched for each timeframe, that is, for each period in which a candle was activated.
Perfect integration with technical indicators.
Candlesticks summarize the opening, closing, maximum, and minimum phases of a given title over a given period. Each of them consists of:
- Body: starts from the opening price and reaches the closing price
- Tail: it connects the maximum and minimum of the seat with the candle body
To draw a Japanese candle chart, we will then need all OHLC prices.
Then we go to select Date – Open – High – Low – Close, and then we go to insert and choose the Japanese candlestick chart.
Again, we can adjust the maximum and minimum of the X-axis, as we have seen for the line graph.
For better chart visibility, we start with formatting the data that we have imported from Yahoo Finance.
Then format the cells of the columns “Open,” “High,” “Low,” “Close” as a number, and set the decimal places to “2” and click Ok.
Go to the cell A1 and press “Ctrl” + “Shift” and click “Right Arrow Key” and after “Down Arrow Key” to select all data to range from Date to Close; this is a useful shortcut for Excel.
Then in the “Insert” menù, click on Stock Chart.
Select “Stock” Open – High – Low – Close
The stock chart is ready.
Dress up your Excel Stock Chart
Now we can modify it. Select and change the Chart Title, for example, in “SPY DAILY CHART.” Select Open High Low Close (Blue Arrow) and click “Canc” on your keyboard.
Click to select an Up Bars; the menu “Format Up Bars” will opens to the right. Choose a green color.
Select the Border option and choose the Black Color
Do the same with the Down Bars and color it with red and black.
Finally, modify the transparency of the Major Gridline.
That’s all. You have created a perfect stock chart like that of the best trading platforms.
Do you want to add an indicator to your stock chart? In this tutorial, you can learn how to create the Average True Range Indicator.
Do you need to import data from TradeStation to Excel? This tutorial is for you.
Download the Excel file of this tutorial from this LINK.
Editors’ Recommendations:
- SuperTrend Multi Time Frame Indicator
- How to AutoTrade Divergences
- SuperTrend for TradeStation and MultiCharts
