Data sources for quotes

Note - this page describes the behavior of the PowerShell scripts that were created by Maxim specifically for Portfolio Slicer. We adding documentation about Maxim's script behavior after realizing that they are used by the majority of Portfolio Slicer users.

General information

Portfolio Slicer needs historical quotes data about EACH SYMBOL you will be tracking. The good news is that thanks to the users (especially Maxim), there are scripts available that will get quotes for the majority of popular North America and other market stocks/ETFs/mutual funds. As one of the first steps in setting up Portfolio Slicer, you will have to identify what is the best source for your quotes. This page will describe how to choose data source for your quotes.

Here are some rules that you should be aware of:

  • You must remember that for every symbol that you have quotes in “\Quotes” folder and sub-folders, you must add them to the symbol table in Excel file before you start refreshing Excel data. Failure to do so could generate error messages on refresh. If you are using Excel 2010, then if you have quotes for the symbols that are not in Excel Symbol table, you will get an error message on refresh and Portfolio Slicer might use much more memory than expected. Later Excel versions (2013 / 2016+) might not give you an error message on refresh, but we still do not recommend to have such setup. If you have different setup - when you have Symbols defined in Symbol table, but do not have quotes for them in “\Quotes” folder, that will not generate any errors or technical issues, but any reports on that symbol transactions will usually show that symbol equity value is 0 or empty.
    Note: If you are using SymbolRename section in psConfig.txt to rename symbols, the symbol must be added to Excel Symbol table as it would appear AFTER RENAMING, because of generated \PSData\Quotes.csv file will have renamed symbol values.
  • After running data extract scripts, you must review files in “\Quotes” folder and confirm that you extracted quotes are as expected. For that you should understand how quote date is stored locally.
    • First, make sure that minimum date in your quotes file is as you set up. If your source did not provide quotes from your symbol start of the trading date, you might have to choose different data source, or you might have to add missing data manually.
    • Second, make sure that quote price seems reasonable. If the price does not make sense, it is possible that you used wrong symbol/ticker. Or, for some symbols (especially UK symbols) often price might be in pence and not pound, so you would have to configure symbol parameter to apply FactorHistory and/or FactorIntraday value as 0.01. Please note that having Factor value will not change data in symbol quote file, but will change values in final generated file \PSData\Quotes.csv.
  • Then check PSData folder (default “c:\PortfolioSlicer\PSData\“) and make sure that there is no error.txt file in that folder. If there is an error.txt file, that means that there are some problems with your data and content of that file will explain what errors were found. When all data is extracted from different data sources, one of the last steps is to delete all files from PSData folder, create new data files and then run a check on these re-created files. If any issues are found, then error.txt file is created and all issues are listed there. You must never attempt to refresh Portfolio Slicer Excel workbook if you have data in source files as in some rare cases that might corrupt Excel workbook!

When you are choosing data sources for your symbols, always research EACH symbol separately and confirm that you extracted data that you want.

Quote Source - Generated Quotes

If you have any symbols that are always traded at the same price (for example Guaranteed Investment Certificates, GICs), then you can list them in “GeneratedQuotes” section of the psConfig.txt file. Existing scripts will create one quote per month for each symbol and that is sufficient for Portfolio Slicer to work properly. Here is how you would configure symbol TDB166C.TO with static price of 10$ and trading start date of 2012-10-01:

When the final PSData\Quotes.csv file will be created, all generated quotes will be appended to the end of that file:
To complete setup, in Portfolio Slicer Excel workbook you would add this symbol to the Symbol table with CAD currency.

Quote Source - Yahoo Finance

It is our recommendation, that you attempt to use Yahoo Finance as the primary source for your data for Portfolio Slicer. Yahoo Finance scripts work fast, and are able to extract historical and intraday quote data and also dividends.

To check if Yahoo Finance can provide data about your symbol please visit website https://finance.yahoo.com and in the search bar start entering ticker or name of the company. After you typed a few letters, you will see a list of companies that you can choose from:

After your choice, you will see symbol (usually next to the company name) that is used by Yahoo for that company. Please note, that for symbols that are traded outside of North American markets, there will be suffix attached identifying the trading market. For example symbol XIU.TO represents a symbol in the Canadian market (TOronto), symbol AAPL.MX represents company “Apple Inc” in Mexico market and symbol AAPL represents company “Apple Inc” in USA.

To confirm that Yahoo Finance has historical prices for that symbol, click on the “Historical Data” link:

If you see daily quotes for that symbol and “Download data” link, then you should use Yahoo Financial as a data source for your symbol. If you see quotes just for last day, then Yahoo Financial is not a good source for quotes for your symbol.

Quote Source - Stooq

“Stooq” is Polish data provider that has quotes for many European and North American symbols. The website interface is mostly in The Polish language, but it is intuitive enough to do simple tests. To check if Stooq has quotes for your symbol please visit website https://stooq.com and in the search box start entering the name/ticker of your symbol and then from the list choose a symbol that is most closely represents what you are looking for and then click button “Kwotuj”:

Please note that symbols used by Stooq will be slightly different from the same company representing symbols in Yahoo Finance. For example, in Stooq symbol for “Apple Inc” in North America market will be AAPL.US (instead of AAPL in Yahoo Finance).

After identifying your symbol, you should check if Stooq has historical prices for it. For that on the left bottom click on the link “Historical data”:

If you see the table with daily quotes and at the bottom of the table you see a link “Download data in csv file…”, then you can use Stooq as a data source for your symbol.

Quote Source - AlphaVantage

AlphaVantage is another good data source for quotes. This website requires that to use their service, you first must register and get unique string called “API Key”. For that you just need to provide the name and email address. To get this key please visit website: https://www.alphavantage.co/support/#api-key, enter your information and after hitting “Get Free API Key”, you will get a message:

Welcome to Alpha Vantage! Your API key is: XUEX5N7NLYYU9ZO0. Please record this API key for lifetime access to Alpha Vantage.
Please record that API Key as you will need it to get any data from this website. Edit psConfig.txt file, find section “AlphaVantageKey” and add there your API key:
AlphaVantage does not provide any search interface to search for symbols, but our experience shows that symbol will have same naming as Yahoo Finance website. Easiest way to see if AlphaVantage has data for your symbol is by adding symbol to psConfig.txt “AlphaVantage” section, running extract and checking if symbol quotes file was created in “\Quotes” folder.

Another way to check if AlphaVantage has quotes for your symbol is to edit following url that gets data from this data source and then copy that url to any browser and review file returned:

https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=AAPL&apikey=demo&datatype=csv&outputsize=compact
In above string you have to replace “AAPL” with your symbol and replace “demo” with your API Key.

Quote Source - Google/GoogleWeb

As of Oct, 2017 Google stopped providing quote data, so we will not give more details on this data source.

Symbol rename

In psConfig.txt file there is a section “SymbolRename” that allows you to rename symbols after quotes are merged in the final \PSData\Quotes.csv file. For example you extracting AAPL.US symbol quotes from Stooq website, but would like to use symbol AAPL in Excel workbook. In such setup your quotes would be stored in \Quotes\ _AAPL.US_.txt file and inside of that file symbol would be AAPL.US, but when PSData\Quotes.csv file is created, all AAPL.US symbols would be renamed to AAPL.

To use this functionality you simply would add string “OldSymbol,RenamedSymbol” to Symbol rename section, for example:

<SymbolRename>
AAPL.US,AAPL
TSE:XIU,XIU
</SymbolRename>

How to change the quote source

Sometimes you will need to replace your symbol quote source. When you do that, you must make sure that final created PSData\Quotes.csv file has no duplicate values and used symbol is consistent.

If the symbol is exactly the same between 2 sources (example AAPL in Yahoo Finance and Stooq), then you can simply edit psConfig.txt and move symbol from one section to another (example from Yahoo section to AlphaVantage section). But if the Symbol changed between sources (example AAPL in Yahoo Finance and AAPL.US in Stooq), then there is a little bit more work involved in changing data source.

Let’s say you have symbol “AAPL” and you get quotes from Yahoo Finance website. From Oct 1st, 2017 you want to change quote source for this symbol to Stooq. You already have symbol AAPL defined in Portfolio Slicer Excel workbook as “AAPL”. Here is how you can accomplish this:

  • Option 1 - re-extract all data. In psConfig.txt file move AAPL from Yahoo section to Stooq section and change AAPL symbol to AAPL.US. In the folder “\Quotes\” delete (backup first!!!) files _AAPL_.txt and _AAPL__Archive.txt. To SymbolRename section add string “AAPL.US,AAPL”. Re-run extract for external data.
  • Option 2 - rename existing data. In psConfig.txt file move AAPL from Yahoo section to Stooq section and change AAPL symbol to AAPL.US. In the folder “\Quotes\” rename files _AAPL_.txt to _AAPL.US_.txt and file _AAPL__Archive.txt to _AAPL.US__Archive.txt. Open these files in the notepad and replace string “AAPL” with “AAPL.US”. To SymbolRename section add string “AAPL.US,AAPL”. Re-run extract for external data. In such setup scripts for Stooq will just append data to existing quote files.
  • Option 3 - combine old and new data. In psConfig.txt file move AAPL from Yahoo section to Stooq section and change AAPL symbol to AAPL.US and add SymbolMinDate, like this: “AAPL.US,2017-10-01”. In the folder “\Quotes\” copy files _AAPL_.txt and _AAPL__Archive.txt to the subfolder “\Quotes\Manual\“, open each file and make sure there are no quotes after 2017-09-30. To SymbolRename section add string “AAPL.US,AAPL”. Re-run extract for external data. In such setup, old quotes are moved to another folder and new quotes are extracted just from specified date and then these quotes are merged when they are renamed.