In our Excel Stock Chart Tutorial you will learn how to:
- extract data from Yahoo Finance to Excel
- plot the Stock Chart in Excel
- create many Technical Indicator with Excel Formulas (Coming soon)
Whether or not you have a trading platform, using Excel for technical analysis of a financial instrument is undoubtedly a good idea.
Microsoft Excel performs excellent data processing work through a Spreadsheet.
Excel is a reference standard in the world of spreadsheets as it is the most powerful and flexible tool.
Microsoft Excel is a vast library of functions, and it is also possible to expand its functionality thanks to external add-ons.
Remember also that Excel can be programmed through VBA code.
Excel works in both Windows and Mac environments, and lately, you can use it effectively from mobile.
Why use Excel to create and analyze a Stock Chart
With this Excel Stock Chart Tutorial, you can create tools that help the trader make better decisions.
When we need to set up our stoploss, we may need some additional data.
With Excel, you can view Japanese candle charts, but you can also use other, more distinctive charts such as Heikin Ashi.
The Heikin Ashi is an extraordinary chart that is becoming increasingly successful.
It is also possible to attach almost any indicator on the leading trading platforms to the graph.
But if I already have all the tools on my platform, why use excel?
First of all, Excel allows you to customize every aspect of the indicators.
Indeed, indicators can also be modified on platforms, but the programming language is not always accessible and straightforward.
Each platform has its language, some derive from other programming languages such as C++ or C-Sharp and are proprietary languages usable only on that platform.
For example, TradeStation, which is the most famous trading platform in the world, uses the proprietary language EasyLanguage.
MultiCharts also use this language under another name. EasyLanguage is simple enough to learn; on our website, you will find many free tutorials.
Other platforms like MetaTrader have a much more complex language that needs more commitment to learning.
With Excel, some formulas are more easily adaptable and editable, and creating an indicator is undoubtedly more comfortable than with other programming languages, EasyLanguage excluded, of course.
Excel is also often used for screening a basket of actions. Again, many platforms cannot use the market screener, or in any case, it is a paid service.
Daily or Intraday data – Free and paid Datafeed
As you know, we often use MultiCharts or TradeStation for our analyses. Still, we want to enable all traders to perform studies in a wholly untied manner from any platform or broker.
In our Excel Stock Chart Tutorial, we will mainly use daily data. This data is easier to find and will hardly present errors so severe as to compromise our analyses.
Of course, you can also use intraday data, but finding it free and reliable at the same time is not an easy task.
You can still extract data from paid sources and analyze it with Excel. Also, in this case, it could be useful to carry out statistical analyses that the trading platform is not able to do.
But the main reason many traders use Excel is the ease of importing market data for tools that are generally not available on major platforms. Or they have high costs to buy market data.
The purpose of this series of tutorials is, therefore, to make you autonomous and independent of any trading platform.
To do this, you will encounter some difficulties that we will overcome together.
Part 1: How to extract data from Yahoo Finance to Excel
The first problem you will face will be the difficulty in finding the data of the financial instrument to be analyzed.
This is one of the main aspects; without the data, we would have nothing to analyze.
The data will also have to be of quality; otherwise, the results of our tests will be falsified.
Many paid services are mainly aimed at professionals because the costs are not very affordable.
There are also cheap data packets, but consider that on average for the American market, it will be necessary to spend about $50 per month.
Many brokers allow you to use market data for free as long as a minimum amount of commissions is generated.
So if your trading activity is frequent enough, you can get free data on many platforms.
This paid data can only be used within the platform or can be exported in the form of CSV files and imported into Excel.
But let’s start with the free services. There are plenty of them, you can find hundreds on the web, but we need to know which one to choose.
To make no mistake, let’s start with Yahoo Finance, which is the world’s most famous financial data portal.
Thanks to this tutorial, you will learn how to extract data from Yahoo Finance, how to process it, and then use it in Excel.
From Yahoo Finance you will extract from the in OHLC format, the file includes the following data:
- Close Adj
These data are not to be considered reliable, so we recommend using them as analyses and verifying the real market prices before carrying out any operation.
Part 2: How to plot the Stock Chart in Excel
You have extracted and imported the data, now you can create graphs that are entirely similar to those of the platforms.
Indeed, you won’t get the same visual effect as the platforms, but the charts are the same.
But you will have the same results in terms of analysis, and perhaps you will be able to customize the conclusion more and process the results more thoroughly.
Trading platforms typically use a candlestick chart and in many finance sites, you will find the line chart.
With our Excel Stock Chart Tutorial, you can create many types of charts, there are “Stock Charts” whereas you can see you can choose from various views.
By choosing the Excel candle chart, you will be able to view the chart as you would any other trading platform.
You can learn how to create a:
- Line Graph
- Japanese CandleStick Graph
- Heikin Ashi Graph
How to create many Technical Indicator with Excel Formulas
Using Excel to Calculate the ATR StopLoss
You can use Excel to perform some calculations that platforms can’t do.
For example, discretionary traders want to set a stoploss based on volatility to avoid being thrown out too soon.
As you know, you can use the Average True Range ATR indicator to set a volatility stoploss.
So we can have an Atr StopLoss Calculator that allows us to quickly identify the various stoploss levels adapted to the volatility of the financial instrument.