So how do we calculate bita? The best resource that we have in trading that I've come across anyways, trading view, they've made a number of updates on this over the years. And it's really a one stop shop. So if you guys aren't using trading view, I do highly recommended trading view, you can now download into Excel spreadsheets, the actual price data. So when we're calculating beta, we're essentially calculating the volatility of one single asset versus an index to measure its systemic risk, if you will, if you want to look at it that way. So first of all, we need an index, we're going to look at stocks.

So we're gonna look at the s&p 500. Now, if you go to Yahoo Finance, and you look at beat on Yahoo Finance, of let's say, apple, it'll come up let's say 1.6. Usually that beat as a three year beat, I usually what you see online as a three year bita. Now it's always valuable to have a look at beta from a six month perspective, a 12 month perspective, a three year perspective and a five year perspective. And what that'll do is that'll give you a perspective over time of what the volatility or what the relationship is between one asset and an index. So with the s&p 500, we're going to get the five year beta, we're going to calculate the five year beta.

And remember, it's the same process, the only thing that changes is the time horizon and data that you actually measure. So we're going to look at the s&p 500 over a five year period, and we're going to download this data into an Excel spreadsheet, top left hand corner, three lines here, click on that, scroll down there, you'll see export chart data, click on that. And where it says time format, make sure that you click ISO time and hit export. That data will be exported to an Excel spreadsheet as follows. And you'll get the time the open the high, the low, the close. And if you've got any moving averages on the charts, they'll show up as well.

What we're interested in is to close so we can actually delete all this data here. And we just want to keep the close we want to make sure that that we know that that's the s&p 500 We'll put the date here. And we'll let everybody know that that's the date. So the s&p 500 on the date and now we need we're measuring let's say Apple versus the s&p 500. So now we need Apple's price data back over to trading view now and we need Apple's price data. So key in the ticker, AAPL, enter, Apple will come up, and don't forget to click the five year again.

So top left hand corner, we want to export chart data is oh time export. Apple's data will download in a separate spreadsheet. So what we want to do is we want to copy time, and we want to copy the close. And we want to paste that into the sheet that we've already created. So over to the s&p 500 sheet and we want to paste those values in. And what we want to do is we want to make sure that we have the same price data matching up for each one.

So we'll change this close to Apple. So as we know that this is Apple stock and Apple's price data, and what we want to do is we want to key in a formula Right here. So we're going to input the data here for the s&p 500 to match these dates, right, so so in this E one column, we're going to put SPX, for the s&p 500. And in E two, we're going to do the V local function. So that's equals v lookup, open brackets, and we want the date of Apple comma, column A and B, comma, two comma, false. Close brackets, hit Enter.

And what that will do is it will take the price of the s&p 500 on the same date as Apple on the 24th of March 2014. And what we want to do is, we want to copy that all the way down. So what we're going to do now is we're going to put date here. We're going to copy and paste these. So copy and paste it back in and we're going to delete the first two columns. Now what we need to calculate is the return of apple and the return Return of the s&p.

500. So what we're going to do is we're going to key in AAPL for Apple, on a small or for return. And over here we're going to key in SPX, on a small or for return. And now we're going to create the formula for this. So it's equals, it's the open. So the open price here for Apple minus the previous day's Close, close brackets divided by the previous day's close.

And what that'll do is that'll give us the return for Apple from one day to the next, back on the turret on the 31st of March 2014. So we can drag that over for the s&p 500. And we want to click on this little green box, and we want to drag it all the way to the bottom. So remember, this is five years worth of data. And we can change all this now to percentages. So we know what the daily price change was on each of these assets of two decimal places.

Now what we're looking for what beat essentially is, is it's the slope of the line. So there's two different ways we can calculate this. So the first way which is arguably the easiest way. how we've calculated is equals the slope, open brackets, and we want to calculate the y axis. So the y axis is always the individual acid on the x axis is always the index, right? So the y axis it says right here known y axis known x axis.

So we want to start off with Apple's returns. So we want to copy that in hit comma, and we want to get the slope versus the s&p 500. So back up to the top here, and we want to key in s&p 500 returns. And what that does is that gives us a beta of 1.15. That's a five year visa, that's Apple's five year beta versus the s&p 500. And what essentially this means is for every 1% the s&p 500 has moved over the last five years, Apple has moved on average 1.15% right.

So it's about 15%. more volatility versus the s&p 500 The idea would be that you are long higher beat on names versus the s&p 500 in bull markets, and you are along lower beat names during bear markets. So a lower beat name might be a utility company. And a utility company might have 0.5, a beat a five year beat versus the s&p 500. And what essentially that means is, if you went long, a utility company, and you shorted the s&p 500, for every 1%, the s&p 500 may fall, the utility company will only fall a half a percent, and essentially, you're making the spread on that trade. So that's essentially how we calculate the easiest way of calculating beta.

The second way of calculating beta is if we go up to data on our data, if we go to data analysis, and we look at regression, so we click on regression, which is this one right here. And we want the y axis remember the y axis is always the single axis on the x axis would always be the index. So we go to the single asset that we're looking to measure versus an index, which is Apple, select all the data. And then we input the data for x, which is the s&p 500. So let's highlight all of this, we want the output range, we could put the output range as right beside of the top here, right beside our slope. And that's everything that we need to Yeah, that's everything that we need, I want to send you you're gonna see here is down at the bottom here you see variable X variable one, this is essentially your coefficient.

So the beta coefficient would be 1.15852776, which is exactly the same as the slope of the line. So to show you guys what exactly b that is, if I highlight both of these, and we insert a chart, so we're going to insert a scatter chart, so this one right here, so what we're going to do now is we're going to add a trendline to this. So we go to the top Up here into the search column. And we want to look for left key and trend. And it should come up add chart elements. And we're scrolling down here until we find trend lines.

And we're looking for a linear trendline. Essentially, what bita is, let's make this more obvious, let's put it in red, since you want beta is, is it's the slope of best fit between the returns of Apple versus the returns of the s&p 500. So that's essentially what we're looking for when we're measuring beta. And what that can give us is fantastic insights into the volatility of one asset versus an index. And we want to collect a spread on those trades if you're long short, or we want to maximize our returns during bull markets, and bear markets and beat as a fantastic way of measuring that from here. We're going to move on now and we're going to figure out how would we measure the beat of a full portfolio and how would we position ourselves along short using bita encapsulate the volatility in our portfolio so that our returns are protected in all environments.