Download Stock Quotes into Mathcad

This Mathcad worksheet automatically downloads historical stock quotes from Yahoo Finance with a VBScript button. You don’t need to manually import the data with text files.

Simply supply two dates and a ticker symbol (for example, XOM for Exxon Mobil, WMT for Walmart or KO for Coca Cola). Then, click on a button.

Mathcad then connects to Yahoo Finance (using some VBScript) and imports historical stock quotes for every day between the two dates. Some simple string manipulation then places the data into a table, ready for analysis.

You can now manipulate and analyze the data using all of Mathcad’s functionality. For example, you could calculate the Sharpe Ratio, the Value at Risk at a specific confidence level, or even the investment weights in a Mean-Variance Optimized Portfolio.

The VBScript used to connect to Yahoo is decribed below.

The first part of the code assembles a URL from the arguments to the VBScript button (the two dates and the ticker symbol). This URL is essentially a request to Yahoo to return a text file containing the stock data.

Sub PushBtnEvent_Exec(Inputs,Outputs)
   qurl = "http://ichart.yahoo.com/table.csv?s=" & Inputs(2).Value _
   & "&a="_
   & Month(Inputs(0).Value) -1 _
   & "&b="_
   & Day(Inputs(0).Value) _
   & "&c=" _
   & Year(Inputs(0).Value) _
   & "&d=" _
   & Month(Inputs(1).Value) - 1 _
   & "&e=" _
   & Day(Inputs(1).Value) _
   & "&f=" _
   & Year(Inputs(1).Value) _
   & "&q=q&y=0&z=&x=.csv"
    Outputs(0).value =     GetPage(qurl)
End Sub

GetPage(qurl) references the following VBScript function which sends the request to Yahoo

Function GetPage(URL)
  Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
  WinHttpReq.SetTimeouts 15000,15000,15000,15000
  WinHttpReq.Open "GET", URL, False
  WinHttpReq.SetRequestHeader "Cache-Control", "no-cache"
  WinHttpReq.SetRequestHeader "Pragma", "no-cache"
  On Error Resume Next
  WinHttpReq.Send
  If Err.Number = 0 Then
  If WinHttpReq.Status = "200" Then
  GetPage = WinHttpReq.ResponseText
  Else
  MsgBox "HTTP " & WinHttpReq.Status & " " & WinHttpReq.StatusText
  End If
  Else
  MsgBox "Error " & " " & Err.Number & " " & Err.Source & " " & Err.Description
  End If
End Function

If you don’t have Mathcad, then this Excel Spreadsheet imports stock quotes from Yahoo.

The worksheet is in Mathcad 13 format (but I’ve only tested it in Mathcad 15). You’ll need to enable scriptable components when you load the worksheet.

If you use or share this Mathcad worksheet then I would really appreciate a link to https://investexcel.net.

Mathcad Worksheet to Download Stock Quotes from Yahoo Finance