• Unlevered Stock Beta Spreadsheet

Inputs

• Stock Symbol - The Stock Symbol used by Yahoo Finance. For example, "YHOO" is the stock symbol for Yahoo. "MSFT" is the stock symbol for Microsoft. Check out https://finance.yahoo.com for the list of Stock Symbols supported.
• Start Date - The start date in MM/DD/YYYY format.
• End Date - The end date in MM/DD/YYYY format.

Outputs

• The calculation of Daily, Weekly and Monthly returns. If you recall, the Stock Beta Calculator only supports Monthly returns.
• The calculation of the Beta for up to 5 stocks. If you recall, the Stock Beta Calculator only supports the calculation of Beta for one stock at one time.

StockBetaInternal Worksheet

How is the data downloaded?

Sub GetStock(ByVal stockSymbol As String, ByVal StartDate As Date, 
               ByVal EndDate As Date, ByVal desti As String)
    
    On Error GoTo ErrHandler:
    
    Dim crumb               As String
    Dim cookie               As String
    Dim response               As String
    Dim strUrl As String
    Dim DownloadURL As String
    Dim period1, period2 As String
    Dim httpReq As WinHttp.WinHttpRequest
    Set httpReq = New WinHttp.WinHttpRequest
    
    DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
    With httpReq
        .Open "GET", DownloadURL, False
        .setRequestHeader "Content-Type", 
                      "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        .waitForResponse
        response = .responseText
        cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With

    period1 = (StartDate - DateValue("January 1, 1970")) * 86400
    period2 = (EndDate - DateValue("January 1, 1970")) * 86400
    
    Dim counter As Long
    Dim startCounter As Long
    Dim result As String
    Dim dataResult As String
    Dim startResult As String
        
    crumb = Chr(34) & "CrumbStore" & Chr(34) & ":{" & Chr(34) & "crumb" 
            & Chr(34) & ":" & Chr(34)
    startCounter = InStr(response, crumb) + Len(crumb)
    While Mid(response, startCounter, 1) <> Chr(34)
        result = result & Mid(response, startCounter, 1)
        startCounter = startCounter + 1
    Wend
    
    crumb = result
    Dim freq As String
    freq = "1mo"
    DownloadURL = "https://query1.finance.yahoo.com/v7/finance/download/" 
                  & stockSymbol & "?period1=" & period1 & "&period2=" 
                  & period2 & "&interval=" 
		  & freq & "&events=history&crumb=" 
                  & crumb
    
    startResult = ""
    startCounter = 0
    While (startResult <> "Date" And startCounter < 8)
        With httpReq
            .Open "GET", DownloadURL, False
            .setRequestHeader "Cookie", cookie
            .send
            .waitForResponse
            dataResult = .responseText
        End With
        startResult = Mid(dataResult, 1, 4)
        startCounter = startCounter + 1
    Wend
    
    If (startResult <> "Date") Then
        noErrorFound = 0
        GoTo ErrHandler
    End If
    
    dataResult = Replace(dataResult, ",", vbTab)

    Dim dataObj As New DataObject
    dataObj.SetText dataResult
    dataObj.PutInClipboard
            
    Set currentWorksheet = ThisWorkbook.ActiveSheet
    Set currentRange = currentWorksheet.Range(desti)
    dataObj.GetFromClipboard
    currentRange.PasteSpecial
        
    noErrorFound = 1
    
ErrHandler:
    If noErrorFound = 0 Then
        Application.ScreenUpdating = True
        MsgBox ("Stock " + stockSymbol + " cannot be found.")
    End If
    'Resume Next
End Sub

    DownloadURL = "https://query1.finance.yahoo.com/v7/finance/download/" 
                  & stockSymbol & "?period1=" & period1 & "&period2=" 
                  & period2 & "&interval=1d&events=history&crumb=" 
                  & crumb
    With httpReq
        .Open "GET", DownloadURL, False
        .setRequestHeader "Cookie", cookie
        .send
        .waitForResponse
        dataResult = .responseText
    End With

• stockSymbol is the variable containing a stock symbol such as “BAC”.
• period1 and period2 specifies the start date and end date to download data.
• The “cookie” and “crumb” (extracted from response) required is extracted with the following VBA codes.

    DownloadURL="https://finance.yahoo.com/lookup?s="&stockSymbol
    With httpReq
        .Open "GET", DownloadURL, False
        .setRequestHeader "Content-Type", 
		"application/x-www-form-urlencoded; charset=UTF-8"
        .send
        .waitForResponse
        response = .responseText
        cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With

DownloadData subroutine