Now that we have an open spreadsheet with our historical stock data and market data, it’s time to get rid of the stuff we don’t need. This is a good practice to incorporate into your process since you may one day decide to share your data with others… it must be legible.
The only important data we need is the date (Date) and the Adjusted Closing Price (Adj Close), so select and delete everything else. Do this with both the stock data, and the market data.
The reason we want the Adjusted Close is because it accurately and properly reflects the stock’s value. Many financial analysts and economists consider it to be the true price of the stock, including yours truly. If it’s good enough for Warren Buffett, it’s good enough for me!
At this point, I like to change the headings so it is a bit more clear which column represents which data. Also, I’ll move the columns around so they are more organized. For example, I’ll change the “Adj Close” of our stock to “ABC”, and I’ll change the “Adj Close” of our index to “SP500”, so I know which data I’m looking at. Then, I’ll paste the stock data and market data together so they are on the same sheet.
It’s worth mentioning again: make sure the dates for the stock and the market line up perfectly! Your regression will be totally useless if it is off by even a single day, or week, or month!
Now that our data is organized, it’s time for the complicated stuff… calculations and functions.
Calculations & Functions
It isn’t enough that we find just the prices of the stock or the values of the index. After all, a stock price increase of $5 doesn’t tell us much… but a stock increase of 5% tells us quite a bit!
Math doesn’t lie. It helps paint a picture of how a stock may perform in the future given certain conditions. It is a tool.
So, let’s get some calculations down.
I create two new columns called dABC and dSP500 (the reason for this is in math, d means delta, which means “change in“). Then, find how much the stock increased or decreased by dividing the new price by the old price, then subtracting 1.
Formally, the equation looks like this:
This will give you the percentage increase or decrease from one day to the next. For example, ABC had a price of $86.41393 on Monday and $86.70585 on Tuesday. The Tuesday price divided by the Monday price equals 1.003378. Finally, subtract 1, and our answer is a weekly return of 0.3378%.
To repeat this step for all the days in our dataset, click and hold the lower right hand corner and drag it to the bottom of our set. The column will populate automatically, saving the user a BUNCH of time.
Repeat this step for the index, and your data should look like this:
Don’t worry about the uppermost row being empty for our returns; they won’t mess up our data.
Our data is now organized and our calculations are complete! On to the next step.