Market Performance 2022 YTD using Excel

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

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.