trading

Technical Analysis Using Excel

andysinclairLeave a comment
Simple Moving Average (SMA) chart in Excel

Technical Analysis is used by traders and investors to help identify patterns and trends in historical financial prices. This analysis is often used to produce trading signals or to backtest a trading strategy.

Excel should be a great tool for technical analysis, however traders and investors often struggle with three problems:

  • Getting historical price and volume data into Excel.
  • Writing formulas for indicator calculations.
  • Updating their spreadsheet when new price data is available.

These problems are all solved by using the Excel Technical Analysis Toolkit which has just been released as a new feature of the Excel Price Feed Add-in.

The Toolkit provides a library of new easy-to-use Excel Technical Analysis formulas, so there is no longer a need for complicated Excel formulas or obscure and hard-to-maintain VBA.

Getting historical price and volume data into Excel

Usually, this is achieved by either copying/pasting data from a website/datafeed or downloading a file of prices. With Excel Price Feed you can use one of the many historical data formulas to easily pull prices into your spreadsheet.

For example, to pull daily historical close prices into Excel for the S&P500 Index, you can use this formula:

=EPF.Yahoo.HistoricDailyLookback("SPY",200,"DESC",1)

Where 200 is the number of days we need, DESC means display the data in DESCending order, so more recent day first, and 1 means show column headings:

S&P500 historical close prices in Excel

Now that we have the historical price data in Excel, we can now perform some Technical Analysis.

Excel Formulas for Technical Analysis

Suppose we want to look at how Bollinger Bands perform for the S&P500 year-to-date using a 20-period lookback with 2 standard deviations.

Without Excel Price Feed, this is how to calculate Bollinger Bands using standard Excel formulas (note that this shows only one third of the steps required):

As you can see there are a lot of steps and formulas. The equivalent Bollinger Band formula using Excel Price Feed is:

EPF.TA.BollingerBands(prices, dates, lookbackPeriod, standardDeviations)

The first two parameters are simply the close price and date columns of the historical data, and the other two parameters are the number of periods in the lookback and the number of standard deviations.

This is what it looks like in Excel:

Bollinger Bands Excel formula output

Now that the data is in Excel we can easily add an Excel chart to visualize how the bands move with price, and from this we can start to identify support and resistance and potential trend changes:

SPY Bollinger Bands visualized using an Excel chart

Updating the spreadsheet when new price data is available

To produce this data we have used two formulas, one for the historical data and one for the technical analysis. When we want to update the data in our spreadsheet, we simply refresh the spreadsheet. This will result in the latest daily close prices being downloaded and the Bollinger Bands being recalculated.

No more copying and pasting data or formulas.

Excel Price Feed has a built in Refresh button as well as automatic refresh functions:

Excel Price Feed Refresh options.

To find out more about Excel Price Feed head over to the website and try it free for 10 days: https://www.excelpricefeed.com/

Market Performance 2022 YTD using Excel

andysinclairLeave a comment

Just a quick blog post today, looking at market performance for a bunch of stocks so far this year (otherwise known as year to date or YTD).

As a reference point, today (24 March 2022) the S&P500 is at -6.2% and the Nasdaq is at -11% for YTD.

I am using the Excel Price Feed Add-in to download latest price data and start of year prices for a small set of diverse US stocks. I use the live price formula (EPF.Yahoo.Price) in column C together with the historical price formula (EPF.Yahoo.Historic.Close) in column D to retrieve the stock price on the first trading day of the year which was 3rd January.

This produces the following data table (you can see the formula for cell D2 in the formula bar):

2022 YTD Stock Performance in Excel using the Excel Price Feed Add-in

Now that we have the current stock price and at the start of the year we can calculate the % change using a simple Excel formula which works out the difference (C2-D2) as a proportion of D2:

Calculate stock price YTD change in Excel

Now we can apply this formula to the rest of the table and create a simple bar chart to visualize the performance:

YTD stock performance bar chart in Excel

As we can see the tech sector is under-performing, particularly Facebook and Netflix whilst the oil giants Exxon and Chevron are out-performing everything else.

I hope this gives a good introduction to stock analysis using Excel, to find out more about Excel Price Feed head over to the website and try it free for 10 days: https://www.excelpricefeed.com/

Excel: Calculate trading days between two dates

andysinclair3 Comments

Yesterday a customer contacted us asking if our financial markets data Add-in could calculate the number of trading days between two dates. Unfortunately we don’t have this functionality as implementing it is not as easy as you may think.

You need to take into account weekends, which for most financial markets are Saturday and Sunday. Exceptions to this include several markets in the Middle East such as Saudi Arabia where the working week is Sunday to Thursday and the “weekend” is Friday and Saturday.

You also need to account for market holidays which are usually, although not always, also national holidays.

Fortunately Excel provides the NETWORKDAYS function, which:

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.

This function can be used, together with a holiday lookup, to provide the functionality we need.

NETWORKDAYS function in Excel

In the example above we are using a holiday list lookup in column D together with the NETWORKDAYS function in cell B4 to compute the trading days between 21 July 2020 and 1 Dec 2020 for the US market.

The NETWORKDAYS function assumes that weekends are Saturday and Sunday. If you wish to specify a different weekend then you can use the NETWORKDAYS.INTL function.

Building an IG Index watchlist in Excel

andysinclair2 Comments
Excel Price Feed Streaming Watchlist

IG Index / IG Markets provides over 16,000 different markets to trade, including FX, stocks, indices, commodities and cryptocurrencies. Keeping track of potential trading opportunities within this universe is daunting, you really need a way to filter out which markets you are interested in and monitor just those markets.

This is where watchlists can help.

They help you focus on only the markets that interest you and help quickly give you an overview of the overall financial market. For example, I like to monitor a watchlist of major stock indices, currency pairs and commodities. This is especially helpful when I first arrive at my desk in the morning and want to get a quick overview of global market sentiment.

Brokers, such as IG Index, provide basic tools for building watchlists, for example here is an extract from one of my watchlists on the IG website:

IG Web Platform Watchlist

This watchlist is fine for keeping an eye on a small selection of fields such as % day change but what if you want a more customized watchlist, with filters, sorting, visualizations, custom formulas etc?

This is where the Excel Price Feed Add-in comes in.

You can use the power and customisation of Excel using formulas and visualizations together with the live market data formulas provided by the Add-in to build your own watchlist in Excel.

For example, we can use the daily high and low price to calculate the daily range and visualize where the current price is within the daily range.

In the example below I can see that the current level of the S&P500 is near the high of the daily range (83%) and the range today is 70 points:

We can add more markets and more data, including client sentiment, to give us a live view of the market, highlighting the data we are interested in:

At a glance we can see that the major stock indices are little changed on the day and towards the high of their daily ranges. We can also see that the main mover today is the cryptocurrency Ether.

Using Excel like this we can track and compare markets exactly the way that suits our trading style. Once we have setup our spreadsheet it is updated automatically with live market data so it always gives us a live view of the market.

Download Excel Price Feed today and try it for yourself.