How can you use Excel Spreadsheets for Stock Market Analysis?

If I am stranded on a lonely island with a laptop and option to use just one software, then certainly that software will be “Excel”. It is evident that excel can do more than any other software, from simple mathematical calculations to sophisticated time-series analysis, excel has everything packed in. In this post, we’ll learn how to use Excel spreadsheets for stock market analysis.

How to Get Started?

The very first thing that you need to do is to download/install Excel on your computer. The most popular spreadsheet software is Microsoft Excel but it comes with a heavy price tag. Alternatively, you can use OpenOffice Calc or Google Sheets which also serves the same purpose but available free of cost.

Once you have installed, you’ll need to get familiar with the look and feel of the GUI (graphical user interface).

I would recommend going through below YouTube playlist if you want to learn excel from scratch:

https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

However, if you are already familiar with using excel spreadsheets you can skip it.

Loading Stock data in Excel

If you want to use excel spreadsheets for stock market analysis, you’ll essentially need the stock data i.e. its price and volume.

There are two ways to get this data in excel:

1. Manually: Copy the data from the stock exchange’s website and paste it manually in the excel spreadsheet with proper headers.
2. Automatically: Use APIs or macro-based tools to fetch the data automatically from the exchange. See this post to download one such tool

What all Excel can do?

There are a variety of things that can be accomplished using Excel spreadsheets. Once you have loaded the data, you can derive meaningful insights using formulas.

For example – you can calculate the highest high price for last n days OR you can calculate the moving average for a particular period. Literally, sky is the limit if you know what to do with this data.

I would recommend you to go through below articles one by one which contains some practical examples:

How to calculate CAGR in Excel Sheet?

How to calculate XIRR in an Excel Sheet?

How to plot a candlestick chart in an Excel Sheet?

How to calculate Drawdown in an Excel sheet?

How to calculate Stop Loss in an Excel Sheet?

How to Calculate Expectancy in an Excel Sheet?

How to Calculate SIP Returns in an Excel Sheet?

Can you use Excel for Algorithmic Trading?

Algorithmic trading involves ‘programming’ your trading rules so that computer can give you buy/sell signals when those rules are met.

These rules can be coded in a variety of tools and programming languages. And Excel being one of the most versatile software that ever existed on this planet, it is certainly possible to use it for this purpose.

There are a variety of things Excel can do:

1. Connect to realtime streaming sources and fetch OHLCV data of stocks and commodities.
2. Perform manipulation of data using formulas and macros.
3. Backtest a trading strategy.
4. Generate P&L graphs and performance reports.
5. Connect to brokers terminal and place buy/sell orders

These pretty much cover everything that modern algorithmic software can do.

Check out some of our blog posts here that illustrate some of these features.

Learn how to build an automated trading robot in Excel here.

Next Steps

Now since you have a fair understanding of what all excel can do, below would be the next logical steps:

1. Learn using Excel spreadsheets and get yourself familiar with all the important formulas.
2. Learn advanced features of excel like macros, power query, etc
3. Try building your trading rules in excel using formulas or macros
4. Backtest the rules on the historical data
5. Generate meaningful graphs and insights from the data

If you are successful doing all of the above, you’ll very soon master using excel spreadsheets for stock market analysis.

As always, if you are stuck somewhere or need any specific help, feel free to drop a comment.

---