How to Draw a Chart in Excel Using Yahoo Finance

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

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

 

Excel line graph indian stock example

 

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 Nifty 50 Index of the Stock of 50 large Indian companies.

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 in 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 Import Stock Prices from Yahoo Finance to Excel

 

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.

 

Line Graph Excel tutorial

 

Then we click on insert and choose the line graph.

 

Insert the stock chart line excel

 

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.

 

Line Graph Excel with error in datafeed

 

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.

 

excel stock chart tutorial error line

 

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.

 

Line Graph Excel indian stock

 

 

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.

 

right click to arrange the excel chart

 

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.

 

stock chart indian excel

 

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.

 

Excel Bar Chart display

 

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 the number of periods can be significantly reduced without creating problems.

When you zoom out on a candle chart, sometimes you can’t it 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.

 

Excel Stock Chart format column

 

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.

 

Excel Stock Chart

 

Then in the “Insert” menù, click on Stock Chart.

 

how to create stock chart with excel

 

Select “Stock” Open – High – Low – Close

 

select the stock chart with excel

 

The stock chart is ready.

 

excel stock chart from yahoo finance

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.

 

 

Change Excel Stock Chart

 

Click to select an Up Bars; the menu “Format Up Bars” will opens to the right. Choose a green color.

 

Excel Stock Chart modify Up Bars

 

Select the Border option and choose the Black Color

 

Excel Stock Chart Bars border

 

Do the same with the Down Bars and color it with red and black.

Finally, modify the transparency of the Major Gridline.

 

 

 

 

 

Excel Stock Chart modify Major Gridlines

 

That’s all. You have created a perfect stock chart like that of the best trading platforms.

 

japanese candlestick chart excel

 

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.

 

 

MORE FROM FINANCE STRATEGY SYSTEM

Gap – Technical Analysis Tutorial – Gap-Up & Gap-Down

Pivot Points || Essential Tutorial 2020

Ichimoku Indicator | Ichimoku Kinko Hyo | Tutorial

Gold Trading Strategy with Stochastic Indicator

Parabolic SAR Indicator – Technical Analysis Tutorial

CMO Indicator | Chande Momentum Oscillator | Tutorial

Moving Average: All about Moving Averages || 2020 Tutorial

CME settlement prices || What is and how to calculate

Coppock Curve Indicator

Momentum Indicator Oscillator || 2020 Tutorial

Excel Stock Chart and Technical Analysis || 2020 Tutorial

How to create Heikin Ashi Excel Stock Chart || Tutorial

 

 

 

 

 

 

 

 

Subscribe To Our Newsletter

Get updates and learn from the best

Recent Posts

Eurusd Chart Technical Analysis || 2020

In this page, we study Eurusd using a lot of technical analysis indicators and strategies. Eurusd Analysis using RSI Indicator Today we analyze the Eurusd

Donate

My name is Luca. I grew up in Italy. I have a degree in law and I’m an independent trader since 2007. 

I’m a systematic trader and sometimes, I trade using options strategies with US ETFs and Stocks.

I have built hundreds of automated trading systems and indicators for TradeStation, MultiCharts and MetaTrader.

I started this blog in 2017 to share what I learned in the financial market.

Follow us on :-

WhatsApp us whatsapp