Open Source Excel VBA macro for downloading Stock Quotes from Yahoo Finance

 This is an open source Excel Visual Basic for Application macro that automatically downloads data from https://finance.yahoo.com. The macro can be imported into Excel in a few simple steps and configured to download trading data for different stocks.

Download

GetQuotesFromYahooFinance.bas

License

The VBA module is dual licensed under the MIT or GPL Version 2 licenses.

Tutorials
Upcoming

Support Yahoo Query Language (YQL) in the upcoming versions of the macro. For example to get Yahoo Finance data as described in

http://developer.yahoo.com/yql/guide/yql-code-examples.html#yql_html_scraper

Using the macro to download data from Yahoo Finance to an Excel spreadsheet

 1. Right click and save the GetQuotesFromYahooFinance.bas VBA macro file.

2. Launch Microsoft Excel and create a new spreadsheet. Alternatively, you can also perform the steps below from TraderCode Technical Analysis Expert spreadsheet, Strategy BackTesting Expert spreadsheet or Point-and-Figure Charting Expert spreadsheet. The macro will download data to the "DownloadeData" worksheet.

3. Import the GetQuotesFromYahooFinance.bas file to Microsoft Excel.

  • Excel 2007/2010
    Click on the Developer tab and the Visual Basic button on the Ribbon. Select the GetQuotesFromYahooFinance.bas file. Click on Save followed by Close in the Visual Basic Editor
  • Excel 2003
    Click on Tools->Macro->Visual Basic Editor from the menus. Select the GetQuotesFromYahooFinance.bas file. Click on Save followed by Close in the Visual Basic Editor
4. Run the macro

  • Excel 2007/2010
    Click on the Developer tab and the Macros button on the Ribbon. Select the ActiveQuotesDownload macro and click on the Run button
  • Excel 2003
    Click on Tools->Macro->Macros from the menus to bring up the Macro dialog box. Select the ActiveQuotesDownload macro and click on the Run button. Trading stock quotes on Yahoo are downloaded to the "DownloadedData" worksheet

Setting up the macro for different types of trading data

 The open source VBA macro can be setup easily to download data for different stock symbols or data of different start dates and end dates. We assume that you have completed the steps in the "Using the macro to download data from Yahoo Finance to an Excel spreadsheet" section.

1. To download different types of trading data, the ActiveQuotesDownload macro simply requires different input values. This can be setup easily in Excel.
  • Excel 2007/2010
    Click on the Developer tab and the Visual Basic button on the Ribbon.
  • Excel 2003
    Click on Tools->Macro->Visual Basic Editor from the menus.
Note: If you do not see the ActiveQuotesDownload VBA module after performing the step above, identify your VBA project and click on Modules->Module1

2. The ActiveQuotesDownload macro uses the GetStock macro to download data from Yahoo Finance. The input values required for the GetStock macro are Stock Symbol, Start Date (MM/DD/YYYY) and End Date (MM/DD/YYYY). Locate the following line in the ActiveQuotesDownload macro.

Call GetStock("YHOO", "01/01/2009", "01/03/2009")

Simply change the input values to download different types of data. For example, to download trading data of Apple, change the first parameter to "AAPL".

Call GetStock("AAPL", "01/01/2009", "01/03/2009")

3. Click on save in the Visual Basic Editor and then click on the Excel spreadsheet. Run the updated macro.
  • Excel 2007/2010 Click on the Developer tab and the Macros button on the Ribbon. Select the ActiveQuotesDownload macro and click on the Run button.
  • Excel 2003 Click on Tools->Macro->Macros from the menus to bring up the Macro dialog box. Select the ActiveQuotesDownload macro and click on the Run button.
Trading stock quotes on Apple will be downloaded to the "DownloadedData" worksheet.

Other input values supported by the macro

Specifying the frequency of the data

An optional fourth parameter in the GetStock macro allows you to specify whether to download daily ("d"), weekly ("w") or monthly ("m") data. For example, to download daily data, use the value "d" as shown below:

Call GetStock("YHOO", "01/01/2009", "03/03/2009","d")

Specifying whether to swap the Close and Adjusted Close columns

Sometimes, you may prefer to use the Adjusted Close values instead of the Close values downloaded from Yahoo Finance. You can swap the Close and Adjusted Close column by specifying "y" in the fifth input value of the GetStock macro (see examples below) and arrange data in Date, Open, High, Low, Adjusted Close format.

To swap the columns

Call GetStock("YHOO", "01/01/2009", "03/03/2009","d","y")

Do not swap the columns

Call GetStock("YHOO", "01/01/2009", "03/03/2009","d","n")

Specifying the output worksheet

By default, the macro downloads the data to a worksheet named "DownloadedData". If this worksheet does not exist, it will be created automatically. You can specify an alternative worksheet as shown below.

Call GetStock("YHOO", "01/01/2009", "03/03/2009","d","y","Sheet1")

Customizing the open source macro to automatically call TraderCode's Experts after downloading data

 If you have imported the download macro to TraderCode's Experts, you can easily setup the macro to activate the Experts to perform technical analysis, backtesting or Point-and-Figure charting after the data are downloaded.

Technical Analysis Expert

1. Import the open source macro to TraderCode's Technical Analysis Expert.

2. Add the line as shown in bold below. When you run the ActiveQuotesDownload macro, data will be downloaded and the Analysis Expert will then be triggered to perform technical Analysis and charting.

Sub ActiveQuotesDownload()
    	
    Call GetStock("YHOO", "01/01/2009", "03/01/2009")

                    Call Expert.AnalyzeandChart

End Sub

Strategy Backtesting Expert

1. Import the open source macro to TraderCode's Strategy Backtesting Expert.

2. Add the line as shown in bold below. When you run the ActiveQuotesDownload macro, data will be downloaded and the Backtesting Expert will then be triggered to perform analysis and back testing.

Sub ActiveQuotesDownload()
    	
    Call GetStock("YHOO", "01/01/2009", "03/01/2009")

                    Call Expert.AnalyzeAndBackTest

End Sub

Point-and-Figure Charting Expert (Or Advance Point-and-Figure Charting Expert)

1. Import the open source macro to TraderCode's Point-and-Figure Charting Expert.

2. Add the line as shown in bold below. When you run the ActiveQuotesDownload macro, data will be downloaded and the Point-and-Figure Charting Expert will then be triggered to perform charting. 

Sub ActiveQuotesDownload()
    	
    Call GetStock("YHOO", "01/01/2009", "03/01/2009")

                    Call Expert.DownloadedDataChart

End Sub

Brief Explanation of the source code

 In the whole block of code in the bas file, the most important line is the following: With ActiveSheet.QueryTables.Add(Connection:=DownloadURL, Destination:=Range("$A$1"))

It basically says that we will be downloading data from DownloadURL and placing the result into the cell A1. The DownloadURL is constructed based on the parameters explained below.

http://table.finance.yahoo.com/table.csv?s=YHOO&a=01&b=01&c=2007&d=08&e=05&f=2008&g=d&ignore=.csv
  • "s=YHOO" means to download the stock prices of Yahoo. YHOO is the stock symbol of Yahoo.
  • "a=01&b=01&c=2007" specifies the start date in Month, Day, Year. You might have noticed that the month is subtracted with 1, which is the format required by Yahoo.
  • "d=08&e=05&f=2008" specifies the end date in Month, Day, Year. You might have noticed that the month is subtracted with 1, which is the format required by Yahoo.



Back to TraderCode Technical Indicators and Technical Analysis