Free Script to Download Stock Quotes from the Yahoo Finance Website

Portfolio Slicer needs historical quotes to calculate your portfolio values at any point in time. You can use the script provided here to download Stock/ETF/Mutual Fund quotes free of charge from the Yahoo Financial website.

This script will:

• For each symbol specified in \symbol-Yahoo.txt file this script will create historical quote file "\Quotes\Yahoo-History\<symbol>.txt. This file will contains daily quotes from the date you specified (in GetQuotes-Yahoo.ps1 file) up to latest available historical quote date. If this symbol already has historical quote file, then script will find latest available quote date and request and will append quotes after that available date up to today (time this script was run). Normally Yahoo historical quotes return quotes of previous business day, so current day quotes are not included.
• For each symbol specified in \symbol-Yahoo.txt file this script will create "latest" quote file "\Quotes\Yahoo-LastPrice\<symbo>.txt. If we already have quote for this symbol with current date, we will delete older quote and will add latest quote to make sure that we have just one latest quote per symbol per day. We do this extra step so we can get latest up to 20min delayed quotes so our quote file can be as much as possible up to date.
• Script will merge quotes from history and latest quote files into one file "\Quotes\quote.txt". This file will be used by Portfolio Slicer, or it could be used by any other application. Here are first few lines from this generated file:
  

Script setup instructions

• Download the script from the download area of this website.
• Unzip provided files into the location where you will be storing downloaded quotes. After unzipping you should see the following structure (in this sample c:\PowerPivotApps\PortfolioSlicer is the folder were files were extracted to):

Note: If you downloaded just script without Portfolio Slicer, then your folder will contain less files.

• In any text editor (for example Notepad) edit the symbol-yahoo.txt file. Delete the existing lines and enter symbols that you would like to get quotes for. Make sure you enter just one symbol per line. Ensure that symbols are entered exactly how the Yahoo Financial website understands them. For example, you would enter MSFT for Microsoft and XRE.TO for the Canadian real estate index. Save the file.
  
• In any text editor (for example Notepad) edit the GetQuotes-yahoo.ps1 file. Change the minimum start year, month, and day values. Please keep in mind that the month parameter is 0 based. This means that the value 2 means March. Save the file.

• If you have never run PowerShell scripts, change the execution policy for your PowerShell environment. Read more about this here...
• In text editor edit "_refresh-prices.bat" file and comment out script that you do not use (add "REM " to the start of the line. REM=Remarks).
• If you are using this script without portfolio slicer, then you can now execute this script by double clicking on the file "_refresh-prices.bat". If everything is setup correctly, this should get quotes from the Yahoo Financial website to your computer. These quotes will be in the Quotes\quotes.txt file. This file will have price quotes for all symbols that you specified starting from the day you configured in the script. This script will also create many other files in the Quotes folder and its subfolders, but these can be ignored. Do not delete these extra files, as they are used by the script to speed up your quote update.
• This quote file now can be used in the Portfolio Slicer workbook to calculate current portfolio values.
• If you have any problems, please post your questions in the script forum. 

If you are the average user, then you do not need to read any further. The information below is just for those who would like to understand exactly what this script is doing. 

Advanced Information: How this Script Works

The Yahoo Finance website lets you request two types of quotes: "Historical" and "Latest Price". For portfolio slicer we use both types of requests and merge results into one quote file. We want to include the latest price in our quotes so we can analyze our portfolio with the most up-to-date available information. Altough this complicates scripts that get quote data, we felt that it was well worth it. With the latest quote date in Portfolio Slicer you can see how your investments are doing with just a twenty minutes delay.

Yahoo Finance Historical Quote Request

Historical quotes can be requested for any period in the past. To request historical quote an URL in the following format is used:

http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=02&b=13&c=2001&g=d&ignore=.csv

The following lists the parameters of this URL

An example of the returned data is as follows:

Date,Open,High,Low,Close,Volume,Adj Close
2012-05-03,31.88,31.90,31.61,31.76,31501300,31.76
2012-05-02,31.85,31.93,31.64,31.80,37385300,31.80
2012-05-01,32.05,32.34,31.95,32.01,43832300,32.01
2012-04-30,31.98,32.11,31.92,32.02,35697200,32.02
2012-04-27,32.12,32.22,31.88,31.98,41419100,31.98
...

Using the above URL you can request just one symbol quote at the time. As no "Up-to date" parameter is specified, the above url will always get historical quotes up to the current date. 

Yahoo Finance Latest Quote Request

Yahoo Finance also allows latest price quotes that are delayed about 20 min to be requested. Here is a sample URL that will request the latest quotes: 

http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=sl1d1t1c1ohgv&e=.csv

This page explains all of the parameters that can be passed in the request:

For Portfolio Slicer we will request latest quotes using the following URL and parameters:

http://download.finance.yahoo.com/d/quotes.csv?s=MSFT,WMT,VOO&f=d1ohgl1vsc1t1&e=.csv

An example of the returned data is as follows:

5/9/2012,N/A,N/A,N/A,30.76,1850,MSFT,0,4:00pm
5/9/2012,N/A,N/A,N/A,59.03,0,WMT,0,4:06pm
5/9/2012,N/A,N/A,N/A,62.1,0,VOO,0,4:00pm

The parameters used are:

We choose these specific parameters because we want the latest price quote file to contain the same data as historical quote file. 

Local Quote Storage

After we request quotes from the Yahoo Financial website, they are stored locally on the user's machine. There are a few reasons for this:

• We want to minimize the number and size of requests to the Yahoo Financial website. If we are tracking five years of data in our portfolio, the first time we will request quotes for all five years, but after that we will request quotes for just the days since the last request. This will make sure that yahoo financial website servers are not loaded unnecessary and at the same time this will speed up our quote requests.
• For some symbols, the Yahoo Financial website provides incomplete historical quotes, but does provide current quotes (for example: symbol "COS.TO"). In our script we will store one quote (final) per symbol per day and it does not matter if that quote was obtained from historical quote request or from latest quote request. If we identify that the symbol has a quote in the historical and latest quote file, and delete the record from the latest quote file.
• We create one file with all quotes (one line per symbol per day) that will be used in Portfolio Slicer.

We store all symbol quotes in text files. The values inside these text file are comma separated.

Here is how the folder structure looks like:

Main folder with extract scripts
  _refresh-prices.bat (file)
  symbol-yahoo.txt (file)
  GetQuotes-yahoo.ps1 (PowerShell script file that will get historical and latest quotes from yahoo finance website)
  \Quotes\ (subfolder)
  \Quotes\Yahoo-History (subfolder)
  \Quotes\Yahoo-History\MSFT.txt (file)
  \Quotes\Yahoo-History\WMT.txt (file)
  \Quotes\Yahoo-History\.... (other files, one per symbol)
  \Quotes\Yahoo-LastPrice (subfolder)
  \Quotes\Yahoo-LastPrice\MSFT.txt (file)
  \Quotes\Yahoo-LastPrice\WMT.txt (file)
  \Quotes\Yahoo-LastPrice\... (other files, one per symbol)
  \Quotes\tmp-last-price-yahoo.txt (file - temporary file with latest quotes from yahoo for all requested symbols)
  \Quotes\tmp-last-price-merged-yahoo.txt (file - temporary file with latest quotes that are not in history quotes files)
  \Quotes\quotes.txt (file - main file were all quotes are merged) 

File "_refresh-prices.bat"

This is a batch job that executes the PowerShell script to get quotes from Yahoo Financial website. This file contains two lines. The first one executes the PowerShell script and the second one pauses and waits for user to press any key before closing the window.

powershell.exe .\GetQuotes-yahoo.ps1
pause

File "symbol-yahoo.txt"

This file must list all symbols that you want to get quotes for. There should be just one symbol per line. Each symbol should be in the format that yahoo finance website would understand. For example, most quotes from the Toronto stock market should have a ".TO" suffix. The following is a sample of a valid "symbol-yahoo.txt" file:

You can add and remove symbols from this file at any point in time. When a symbol is removed from this file, locally stored quotes will not be affected and will be loaded into Portfolio Slicer. When you add a new symbol into this file, at first our script will request historical quotes from the configured start time and subsequent script execution will ask only for new records.

Folder "\Quotes\"

This is the folder where all quote requests from internet are stored. Temporary files that are used in quote manipulation are stored in this folder as well.

Folder "\Quotes\Yahoo-History\"

This folder contains the quotes received from historical quote requests from the Yahoo Finance website. This folder contains one file for each symbol. File names are <symbol>.txt. For example: MSFT.txt, XRE.TO.txt.

The format of each file is slightly different from the file that is returned from the Yahoo Finance website. In our script we remove the first line with the header and at the end of each line add a symbol. In the new format each line has the following data: Date,Open,High,Low,Close,Volume,AdjClose,Symbol. The following is a few lines from this file:

Folder "\Quotes\Yahoo-LastPrice\"

This folder contains quotes received from the last quote request from the Yahoo Finance website. This folder contains one file for each symbol. The file names are <symbol>.txt. For example: MSFT.txt, XRE.TO.txt. The format of each file exactly matches of the format of the file in the Yahoo-History folder. When the script runs, it is checked if there are already quotes for the same day. If we do, then we delete the same day's older quote and save the new quote. We also check each line in the file and if we find a quote for the symbol for the same date in the historical quotes file, we delete that line from last price quote file. So this file stores just one quote per day when symbol does not have historical quotes for same day.

File "\Quotes\tmp-last-price-yahoo.txt"

This is a temporary file that contains data that was received when last price quotes were requested in the Yahoo Finance website format. This file is left in this folder in case you need to troubleshoot data returned from the Yahoo Finance website.

This file is re-created with each script execution.

File "\Quotes\tmp-last-price-merged-yahoo.txt"

This is a temporary file that contains all of the quotes from the Yahoo-LastPrice folder that do not exist in the historical quotes folder. This file is left in this folder in case you need to track what quotes come from the historical folder and what quotes come from the last price folder.

This file is re-created with each script execution.

File "\Quotes\quotes.txt"

This is file that contains all quotes, one line per symbol per day. We use this file to load quotes into Portfolio Slicer. The first line in this file is the header and then we have all symbol quotes, including historical and latest. Here are the first few lines from a sample file:

File "GetQuotes-yahoo.ps1"

This is the PowerShell script file that will request and locally store historical and latest quotes.

If you are new to PowerShell, read here how to setup you PC to run PowerShell scripts. Please note, this script will run just on a Windows PC. It will not work on OS X. Before you run this script, you will have to setup a few parameters:

$MinStartYear = "2007"; # Default start year for quotes
$MinStartMonth = "11"; # Default start month for quotes. 00=January,01=February, etc
$MinStartDay = "27"; # Default start day for quotes.
$Frequency = "d"; # d-daily, m-monthly
$yahooDateFormat = "M/d/yyyy"

You can download this PowerShell script and the proper sub-folder structure from our Downloads link.

You can also watch video on how to use free script to download stock quotes from Yahoo Financial website