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):

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.

Property Value Description
RefreshStyle xlOverwriteCells Replace cell values
BackgroundQuery False Synchronous query
(wait for response)
TextFileParseType xlDelimited Separate columns with a character
TextFileTextQualifier xlTextQualifierDoubleQuote Value Delimiter =
double-quote (”)
TextFileCommaDelimiter True Column 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:

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:

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:

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

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.

 

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:

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”.

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.

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:

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

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