So I was doing some research on the S&P 500 and came across some interesting data. Basically, I wanted to know what the stock market does on average. I particularly wanted to know the percentage of how many days the stock market is up vs down.
Getting my data was actually simpler than you might think. I went straight to Yahoo Finance and entered S&P 500 as a search for a ticker. From there I selected the “Historical Data” tab and then selected a time period of 20 years: from January 1, 1996 – January 1, 2016.
Unfortunately that data didn’t have the % return for each day, but it wasn’t much trouble to find that out. I exported the 20 years of data into an Excel Spreadsheet. With this option, I was able to make quick judgements and conclusions with a little manipulating.
In the Excel Spreadsheet, I created a quick formula in a new column. To make this easier to type and understand, assume x = adjusted close and y = adjusted close the previous day. So my formula was =(x – y) / y. This gave the return % for the day. I simply dragged this formula down the entire column so I had return % data for 20 years.
This is great, but who has time to count each number and tally up the positive and negative dates? I certainly didn’t, so I found this out in a quick and clever shortcut.
I made a new formula in the column next to return %. Assume Hx refers to the cell(s) in the return % column. It was =IF(Hx > 0, 1, 0). Basically this means that if the return % is positive, put a 1 in the cell of the new column. If the return % is negative, put a 0.
Dragging this down the column (which you can do by clicking the bottom right hand corner of the cell and holding it down and moving the mouse down), I had 1s and 0s for every day of the 20 years. Now all I had to do was select every cell in that column, and look at the “Sum” at the bottom of excel. This gave the number 2683.
Next I needed the total number of days. Remember that the stock market is closed on the weekends and holidays, so it wasn’t as easy as 365 * 20. So I made a new column with the first cell having a value of “1”, and dragged it all the way down. The “Sum” of that was 5035. [click to continue…]