Sélectionner une page

Mastering Yahoo Finance with Excel VBA

par | Mar 11, 2022 | API, Excel VBA

With the success of our first article about Yahoo Finance and Excel VBA, we decided to write another (shorter but more precise) article about this topic. This time, we are going to show you how to easily download stock quotes information in real time from the Yahoo Finance API (quotes.csv) using Excel VBA.

In this article, we are going to reuse the code produced in the previous article. So, if you are interested in how we come up with the “magic” VBA macro called “LoadExternalData” presented below be sure to check out the original article.

You can find the whole source code of this tutorial here on GitHub, or you can download the following Excel file that contains the VBA code together with the Dashboard and a list of stock symbols:

yahoo-stock-quotes.xlsm

Content:

  1. Yahoo Finance: understanding the URL parameters (Symbols and Tags).
  2. Main VBA Macro: the code that does the job.
  3. Dashboard: building a user-friendly, Excel application.
  4. Extra features: improving user experience.

Yahoo Finance

Yahoo has several online APIs (Application Programming Interface) that provides financial data related to quoted companies: Quotes and Currency Rates, Historical Quotes, Sectors, Industries, and Companies. VBA can be used to import data automatically into Excel files using these APIs. In this article, we use the API for real-time stock quotes and currency rates (quotes.csv). If you are interested in the API for historical quotes (table.csv), please, visit the previous article.

First, we need to understand how to use the URL that provided stock information. It is of course possible to access Yahoo Finance’s stock data using a Web browser. You can therefore copy the following URLs to download stock data.

To make a request to the CSV API, we are going to use a URL with the following form:

http://finance.yahoo.com/d/quotes.csv?s={SYMBOLS}&f={TAGS}

where {SYMBOLS} must be replaced by a list of known stock symbols (such as GOOGL, AAPL, IBM, etc.) separated by “+” characters, and where {TAGS} must be replaced by letters and numbers describing the type of data to download.

Hence, this URL can be divided into two parts that are separated by a question mark “?”: (1) the static part of the URL that stays unchanged [http://finance.yahoo.com/d/quotes.csv], and (2) the dynamic part that can be modified to download all kinds of data about different companies and currency rates [s={SYMBOLS}&f={TAGS}]. If you do not know about URL formatting, head to the more detailed article.

Here is an example of valid URL that can be used to download various stock information, i.e. symbol, names, stock price and data, about some companies, namely IBM, UBS, Microsoft, Apple, and McDonald’s.

http://finance.yahoo.com/d/quotes.csv?s=IBM+UBS+MSFT+AAPL+MCD&f=snl1d1

This request will create a CSV file (quotes.csv) that contains the following data:

"IBM","International Business Machines",161.38,"4/20/2017"
"UBS","UBS Group AG Registered Ordinar",15.585,"4/20/2017"
"MSFT","Microsoft Corporation",65.17,"4/20/2017"
"AAPL","Apple Inc.",141.99,"4/20/2017"
"MCD","McDonald's Corporation Common S",133.11,"4/20/2017"

As you can see this is a comma-separated CSV file and, thus, depending on your Windows Regional Settings, Excel might not correctly interpret that file as illustrated below.

Note: You should have US settings for Excel to correctly parse the CSV file.

As you can see, the returned CSV file does not contain headers (column names) as opposed to other Yahoo Finance API and each line contains information about a specific company. The number of lines contained in the file depends on the given number of stock symbols that you passed as the “s” parameter, while the number of column depends on the number of tags passed in the “f” parameter. This means that the resulting CSV file can potentially have any number of columns and rows depending on the values contained in the URL parameters.

In our example, we have stock symbols for 5 companies defined in parameter “s”:

  • IBM: International Business Machines
  • UBS: UBS Group AG Registered Ordinar
  • MSFT: Microsoft Corporation
  • AAPL: Apple Inc.
  • MCD: McDonald’s Corporation Common S

A list of stock symbols (aka. tickers) for parameter “s” can be found here or you can directly download the following file that contain the exhaustive list of symbols: yahoo-ticker-symbols-2016.

In our example, we have 4 different types of financial data:

  • s: Symbol
  • n: Name
  • l1: Last Trade (Price Only)
  • d1: Last Trade Date

A list tags for parameter “f” can be found here or also here.

Therefore, by respectively concatenating tags and symbols together (where symbols are separated by “+” character), we obtain the following parameters and values for the dynamic part of the URL:

s=IBM+UBS+MSFT+AAPL+MCD&f=snl1d1

which in turn gives the following URL that can be used to download data from Yahoo Finance:

http://finance.yahoo.com/d/quotes.csv?s=IBM+UBS+MSFT+AAPL+MCD&f=snl1d1

Main VBA Macro

To download stock quotes from Yahoo Finance automatically, the key idea is to write a short yet powerful macro based on the QueryTable object for importing text file (which corresponds to the returned CSV file) and using the above URL.

This gives the following “magic” piece of code that can be used to download any type of online CSV file (providing the CSV format is similar):

Sub LoadExternalData()
    Dim url As String
    url = "TEXT;http://finance.yahoo.com/d/quotes.csv?s=IBM+UBS+MSFT+AAPL+MCD&f=snl1d1"
    
    With ActiveSheet.QueryTables.Add(url, Range("$C$3"))
        .RefreshStyle = xlOverwriteCells
        .BackgroundQuery = False
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .Refresh
    End With
End Sub

In this Macro, we declare a “url” variable that contains the connection string, i.e. the Yahoo Finance’s URL, concatenated with prefix “TEXT;” that indicates the type of data source (here, a text file).

The QueryTable’s properties and values used in the above macro are described in the following table. They basically tell how Excel should treat and import the CSV file.

PropertyValueDescription
RefreshStylexlOverwriteCellsReplace cell values
BackgroundQueryFalseSynchronous query
(wait for response)
TextFileParseTypexlDelimitedSeparate columns with a character
TextFileTextQualifierxlTextQualifierDoubleQuoteValue Delimiter =
double-quote (”)
TextFileCommaDelimiterTrueColumn Separator = comma (,)

Finally, calling the Refresh method executes the data importation. If you wish to know how we came up with this macro, please, check out the detailed article here.

Not only is this solution elegant, it is also simple and very efficient! Typically, in this scenario, hard-core programmers would use a potentially slow loop to parse the file, while business user will blindly generate a macro and try to modify it in the hope to import the data. As a result, far too often we cross badly designed macros on many forums and websites, which are often produced in haste and without much understanding (e.g. using the WinHttpRequest object). Worse, most people think they know how to code in Excel VBA, while they do not.

Voilà! You have successfully imported external data from one of Yahoo Finance APIs using Excel and VBA. Now, the last step is to provide a dashboard so that users can directly interact with Yahoo Finance without having to modify the connection string contained in the VBA macro.

Dashboard and user interface

In this last chapter, we are going to build an interface in Excel so that end-users can directly interact with Yahoo Finance without having to modify VBA macros. We are therefore going to:

  • Read user input from specific Excel cells,
  • Use these input values to build a valid Yahoo Finance’s URL,
  • Download the appropriate stock data based on the given URL.

In our Excel application, we have three separated Worksheets (called Dashboard, Symbols, Tags) that hold the imported data, stock symbols and Yahoo’s tags, respectively.

The Dashboard sheet is the only sheet with which the user will interact. It holds a single command button called “Refresh” that is used to download some stock details about some given companies.

The Dashboard sheet looks as follow:

In addition to the “Refresh” button, users must select stock symbols representing the companies and tags representing the type of data to download. Companies are selected in the first column, while data types are selected in the row.

To avoid runtime errors, it is crucial to restrict values that users can enter. This can easily be done thanks to Data Validation. We use therefore Data Validation to ensure that symbols and tags are selected from the given lists stored in the two other Worksheets, namely “Symbols” and “Tags”.

The first column must be validated as follow, based on the given list of symbols.

While the first row must be validated as follow, based on the given list of tags.

Additionally, it might be useful to also display the actual names represented by symbols and tags. To this purpose, we add Excel formulae that display the name corresponding to the adjacent symbol or tag. This can be done using the VLOOKUP function.

To look for a symbol, we used the following formula in column B:

=IFERROR(VLOOKUP($A3;Symbols!$A$1:$C$25243;2;FALSE); » »)

To look for a tag, we used the following formula in the second row:

=IFERROR(VLOOKUP(C$1;Tags!$A$1:$C$88;2;FALSE); » »)

Both formulae will display the corresponding name if found; otherwise nothing is displayed.

At this stage the user interface is ready and stock data are imported as a table at cell C3, where each row corresponds to a specific company and each column to a specific data type.

User inputs and URL string

Now that we restricted user inputs, the next step is to correctly read these inputs to build a valid URL.

There are basically two things to read:

  • Symbols contained in the first column
  • Tags contained in the first row.

Counting the number of rows and columns

The first step is to dynamically determine the number of symbols (rows) and tags (columns): the user can select an arbitrary number of companies and data types. You can easily find the last in-use row and column thanks to the UsedRange property of the Worksheet object as follow:

' Count number of rows and columns
Private Sub ComputeTableSize()
    With ActiveWorkbook.Sheets("Dashboard")
        rowCount = .UsedRange.Rows.Count
        colCount = .UsedRange.Columns.Count
    End With
End Sub

An alternative is to use the End property of the Range object starting from cell A3 and going down to count symbols, and from cell C1 and going right to count tags:

With ActiveWorkbook.Sheets("Dashboard")
    rowCount = .Range("A3").End(xlDown).Row
    colCount = .Range("C1").End(xlToRight).Column
End With

However, as you might see in the final version of our VBA project, UsedRange is probably a more robust solution.

Getting user inputs

The next step is to read symbols and tags entered by the end-user. We are going loop over the first row to read all tags and over the first column to read all symbols. To this extent, we wrote two distinct loops that read and concatenate every non-empty value encountered in those cells.

For tags, we built the following function that returns a string of concatenated tags:

' Concatenate tags.
' @return: string containing tags (with no separation character).
Private Function GetTags() As String
    Dim j As Integer
    Dim t As String
    Dim tags As String

    ' Loops on tags and add them to the list.
    tags = ""
    For j = 3 To colCount
        t = ActiveWorkbook.Sheets("Dashboard").Cells(1, j).Value
        tags = tags & t
    Next j

    GetTags = tags
End Function

And for symbols, we built the following function that returns a string of concatenated symbols separated by “+” characters:

' Concatenate symbols (aka. tickers).
' @return: string containing symbols separated by "+" characters.
Private Function GetSymbols() As String
    Dim i As Integer
    Dim s As String
    Dim symbols As String
    
    ' Loops on symbols and add them to the list.
    symbols = ""
    For i = 3 To rowCount
        s = ActiveWorkbook.Sheets("Dashboard").Cells(i, 1).Value
        
        ' concatenates only if there is a symbol.
        If s <> "" Then
            symbols = IIf(symbols = "", s, symbols & "+" & s)
        End If
    Next i

    GetSymbols = symbols
End Function

As you can see these two macros are quite similar, as the first one looks for tags in the first row and the second one looks for symbols in the first column. Note also that tags are concatenated “as is” (i.e. without adding any other character), while symbols are separated by “+” characters.

Values returned by these functions need to be passed as parameter in the URL string before to be sent to Yahoo Finance. To this extent, we built a third function that calls the previous functions in order to build a valid URL.

' Build a valid Yahoo Finance's URL based on the given symbols and tags.
Private Function BuildURL()
    Dim url As String
    Dim symbols As String
    Dim tags As String

    ' Concatenates symbols and tags
    symbols = GetSymbols
    tags = GetTags

    ' Inserts symbol and tag lists as parameters
    url = "http://finance.yahoo.com/d/quotes.csv?s=[s]&f=[f]"
    url = Replace(url, "[s]", symbols)    ' e.g. GOOGL+AAPL+FB+AMZN
    url = Replace(url, "[f]", tags)            ' e.g. s0n0l1d1t1

    BuildURL = url
End Function

Downloading stock quotes

Once the URL for Yahoo Finance is built, we can pass it directly to the macro in charge of downloading online stock data:

Dim url As String
url = BuildURL             ' get symbol and tag list and return valid URL
Call LoadExternalData(url) ' download online data from Yahoo Finance

Finally, we just have to modify our original LoadExternalData macro, so that it takes a URL string as input parameter and downloads the data into cell C3 in the main Worksheet “Dashboard”.

' Download stock quotes from Yahoo Finance.
' @param url: valid URL with parameters, pointing to the Yahoo Finance API.
Private Sub LoadExternalData(url As String)
    Dim q As QueryTable
    Dim s As Worksheet
    Dim r As Range

    ' Set destination sheet and destination range for the returned data
    Set s = ActiveWorkbook.Sheets("Dashboard")
    Set r = s.Range("C3")
    
    ' Indicates that the result returned by the URL is a text file.
    url = "TEXT;" & url
    
    ' Creates a QueryTable Object using the destination sheet and range
    Set q = s.QueryTables.Add(url, r)
    
    ' Configures QueryTable and download online data from Yahoo Finance.
    With q
        .RefreshStyle = xlOverwriteCells ' Replace current cells
        .BackgroundQuery = False  ' Synchronous Query
        .TextFileParseType = xlDelimited  ' Parsing Type (column  separated by  a character)
        .TextFileTextQualifier = xlTextQualifierDoubleQuote  ' Column Name Delimiter ""
        .TextFileCommaDelimiter = True  ' Column Separator
        .Refresh  'Initiates data transfert
    End With

    ' Destroys the QueryTable object (since it is used only once)
    q.Delete
End Sub

Congratulation! You have now mastered the Yahoo Finance API.

Extra Features

A software application is never 100% complete, one can always add new features, improve user experience, etc. We present in this chapter some improvements that we made to ease the life of end-users.

Clearing previous data

Clearing previous data before importing new data is essential if you want to display only consistent data based on the user selection. This macro does the job just right: all data starting from cell C3 will be deleted, while the two first columns and rows are preserved.

' Clear imported data while preserving column and row headers.
Private Sub ClearTable()
    If rowCount > 2 And colCount > 2 Then
        ' Only clear table if there are some values
        With ActiveWorkbook.Sheets("Dashboard")
            .Range(.Cells(3, 3), .Cells(rowCount, colCount)).ClearContents
        End With
    End If
End Sub

Note that it is important to only clear the data without the user inputs as they will be needed to build the URL string later on.

Events for better user experience

We also added some events to ease the use of our dashboard and improve user experience (UX). We added two key events that (1) load online data automatically when the file is open and (2) insert formulae automatically in the second row and column to display the actual tag name and symbol name, respectively.

The event to automatically load data is implemented in ThisWorkbook. It simply calls the primary macro, called “Refresh”, contained in the “StockQuotes” module:

Private Sub Workbook_Open()
    ' Automatically download stock quotes when the Workbook opens.
    StockQuotes.Refresh
End Sub

While the second event that automatically fetch names of tags and symbols is implemented in the main Worksheet called “Dashboard”:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As String
    Dim r As Range
    Dim c As Range
    
    ' Check intersection with first row and first column
    Set r = Application.Intersect(Target, Rows(1))
    Set c = Application.Intersect(Target, Columns(1))
    
    ' Insert formula when first column is changed
    If Not (c Is Nothing) Then
        If c.Row > 2 Then
        cell = c.Address
            c.Offset(0, 1).Formula = "=IFERROR(VLOOKUP(" & cell & ",Symbols!$A$1:$C$25243,2,FALSE),"""")"
        End If
    End If
    
    ' Insert formula when first row is changed
    If Not (r Is Nothing) Then
        If r.Column > 2 Then
            cell = r.Address
            r.Offset(1, 0).Formula = "=IFERROR(VLOOKUP(" & cell & ",Tags!$A$1:$C$88,2,FALSE),"""")"
        End If
    End If
End Sub

For this second event, another option would be to directly copy the name instead of inserting an Excel formula, but this would make the VBA code more complex. Instead, we prefer to use the build-in Excel formulae to ease the development and maintenance of our software application.

Note also that other improvements have been made in the final Excel file, such as deleting empty headers or resizing columns automatically to fit cell content. However, this is out of scope and can be discussed in another article.

Conclusion

Voilà! This concludes this article about mastering Yahoo Finance using Excel VBA. If you click on the “Refresh” button of the dashboard, you should obtain a similar result:

You can find the [whole source code] of this tutorial on GitHub, or you can download the following Excel file that contains the VBA code together with the Dashboard and the lists of symbols and tags:

yahoo-stock-quotes.xlsm

Please, feel free to write any comment about this article or future subject about Excel VBA that you would like us to cover. We are looking forward to your feedback.

For a more detailed explanation, be sure to check or previous article about this topic.

References