Download the Workbook: Dynamic Beta Calculator
Beta is a fundamental measure of volatility in Finance. This is especially important when examining assets such as equity securities. You may be wondering, what does the actual number represent. In Finance, we typically associate the market as having a beta of 1. The market, in this case, can be a representative index of one’s country and its largest corporations, such as the S&P 500 (USA) or the CAC 40 (France). If a company has a beta above 1, then that company is theoretically riskier than the market. If a company has a beta below 1, then that company is theoretically less risky than the market. Investors take these numbers into consideration in their fundamental analysis of the company. An investor may also seek to obtain a certain weighted portfolio beta. This is simply the sum-product of the weights in each company multiplied by their respective beta. This could be one rudimentary method of grasping portfolio volatility.
Beta can be calculated in a number of different ways. Most of the time you will see beta being calculated with an Ordinary Least Squares (OLS) method. Don’t let the name scare you, essentially it’s the same thing as the slope of a line (rise over run, y=mx+b, etc.). In this workbook, we use Excel’s built-in Analysis ToolPak in order to run a regression on the price data of two assets. Whenever you run a regression, you have two main components: the x variable (independent variable) and y variable (dependent variable).
We can manipulate the calculation of beta for a given asset in several ways. Below are the variables in a typical beta calculation.
Start Date = 08/01/2016
End Date = 08/01/2017
Frequency = Weekly
Relative Index: S&P 500
We could theoretically change the frequency of the price data which would alter the result of our calculation substantially. We could also change the time frame to equate to 10-years per se. We could also choose the CAC 40 as a benchmark in our calculation. In all of these scenarios, we could get a drastically different result. If you are using different websites to fundamentally analyze a company, it is important to know how they calculate some of their important metrics displayed. For instance, Yahoo Finance uses a 2-year weekly set of parameters in its beta calculations. This is typically the industry norm for calculating beta. It may be wise to take a few minutes to understand how websites such as Finviz or Yahoo Finance calculate their fundamental metrics. A cursory Google search usually will provide the answers to your questions here.
In order to obtain the price data, we will use query from a text file retrieval in VBA. This will allow us to drop unnecessary columns easily. Notice we can also make it so that the user does not need to manually activate the Analysis ToolPak functions we need with two simple lines of code.
Dim entries As Range
Dim col As Integer
Dim sdate, edate As Date
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
col = 1
sdate = Range("C4").Value
edate = Range("C5").Value
'Loop through entries
For Each entry In Sheets("Parameters").Range("C8:C9")
If col = 1 Then
datevar = 1
datevar = 9
qurl = "http://finance.google.com/finance/historical?q=" & entry & _
"&startdate=" & MonthName(Month(sdate), True) & _
"+" & Day(sdate) & "+" & Year(sdate) & "&enddate=" & MonthName(Month(edate), True) & _
"+" & Day(edate) & "+" & Year(edate) & "&output=csv"
'Get Data Via Text File
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & qurl & "" _
, Destination:=Sheets("Data").Range(Cells(1, col), Cells(1, col)))
.Name = _
.FieldNames = True
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(datevar, 9, 9, 9, 1, 9)
.TextFileTrailingMinusNumbers = True
On Error GoTo error_entry
On Error GoTo 0
col = col + 2
To calculate beta in our workbook, we will automate the Analysis ToolPak to adjust to a dynamic range of return data for different selected time frames. All we need to do is pass in a dynamic range which will be referenced through declared variables passed into a Cells range (numeric). We will also use the .Adress() property to refer to the range in a different style format in order to run the regression (Example: “$A$1:$B$12”). The code that will allow us to refer to a dynamic range is displayed below.
Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Data").Range(Cells(1, 4).Address(), Cells(lastRow - 1, 4).Address()), _
Sheets("Data").Range(Cells(1, 5).Address(), Cells(lastRow - 1, 5).Address()), False, True, , Sheets("Parameters").Range("$E2" _
), False, False, False, False, , False
Let’s try out the workbook. We will first select a time frame, and then select our variables in the regression. We will use AT&T as the y variable and the SPY ETF as the x variable. Google Finance’s API doesn’t support requesting data for the S&P 500. The Yahoo historical data API is defunct to any whom may not know. This is the reason we are using the Google API for most of these spreadsheets. We click the form control button and get this output. This post will not go into the interpretation of the outputted numbers in the regression analysis. We will only focus on the intercept which is our value that represents beta in this case.
We can verify this calculation with a few other formulae within Excel. The formula for beta can be represented by the equation below. In the equation, rp represents the return for a stock, while rb represents the return for an index in this case.
We can also use the slope formula within Excel. Note that the results of each of the three calculations are the same.