How quotes data for Portfolio Slicer is stored locally

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.

Quotes data location

When you execute the script to extract quote data from different sources, that quote data is downloaded from some internet source and it is stored on your computer in the folder "<DataRootFolder>\Quotes". You can change <DataRootFolder> value in psConfig.txt file. By default, this parameter value is not specified and that implies that data root folder is "..\" - that is value relative to your script folder location.

Example 1: Your Portfolio Slicer scripts are located in the folder "c:\PortfolioSlicer\Scripts" and in psConfig.txt you have not specified "DataRootFolder" (value is empty).

<DataRootFolder>
</DataRootFolder>

Example 2: Your Portfolio Slicer scripts are located in the folder "c:\PortfolioSlicer\Scripts" and in psConfig.txt you have specified "DataRootFolder" value as "c:\OneDrive\PortfolioSlicer\".

<DataRootFolder>
c:\OneDrive\PortfolioSlicer\
</DataRootFolder>

Quotes file naming

In the quotes folder we will have 2 files per symbol with quotes data:

1. The first file name will be “_Symbol_.txt”. If the Symbol has special characters “:“, “^” or “&“, then these characters will be replaced with character “_“. Examples: For symbol “CCL” quotes will be stored in the file “_CCL_.txt”. For symbol “TSE:XEI” quotes will be stored in the file “_TSE_XEI_.txt”. For symbol “ABC.L” quotes will be stored in the file “_ABC.L_.txt”.
2. The second file name will be “_Symbol__Archive.txt”. If the Symbol has special characters “:“, “^” or “&“, then these characters will be replaced with character “_”. Examples: For symbol “CCL” quotes will be stored in the file “_CCL__Archive.txt”. For symbol “TSE:XEI” quotes will be stored in the file “_TSE_XEI__Archive.txt”. For symbol “ABC.L” quotes will be stored in the file “_ABC.L__Archive.txt”.

Here is example of the file list in the Quotes folder

Quotes Archiving

Normally scripts extracts and stores on your computer daily quotes. But Portfolio Slicer reports are designed so that they need daily quote data just for last 50 calendar days (about 32 trading days) and all quotes older than that can be monthly (that is one daily quote every 30 days). That is why there is a script that reviews file “_Symbol_.txt” and for older months leaves just one quote record per month and other quotes move into the file “_Symbol__Archive.txt”.

In psConfig.txt there are 2 parameters that control quote archiving

• ArchiveQuotes - when the value is set to “Yes”, then quote archiving will be done, otherwise no. The default value for this parameter is “Yes”.
• IncludeQuoteArchiveFolder - when the value is set to “No”, then when the final Quotes.csv file will be generated, all quotes that are stored in the “*__Archive.txt” files will be ignored. The default value for this parameter is set to “No”. So even if you choose to archive quotes, you could set this parameter value to “Yes” and load archived quotes into Portfolio Slicer, because they will be included in the final Quotes.csv file

There is just one reason why we recommend that you keep default parameter values and do quote archiving - better performance in Portfolio Slicer. With fewer quotes to load Portfolio Slicer will work slightly faster and will use less RAM (Memory).

Quotes file content

Symbol quote file contains multiple lines where each line represents symbol closing price for the specified date. Each line in this file has 3 values separated by a comma:

1. The first value is quote date in the format YYYY-MM-DD. Example: 2017-09-16
2. The second value is the closing price at the end of the day. Please note that numeric value should be represented using North American number format, that is using “.” (dot) as decimal separator. Example: 123.45
3. The third value is a symbol. Example: DVY

Here are important rules about quote files:

• Quote files MUST NOT have header line (the first line describing each column).
• Order of the records in the file is absolutely not important. But if for some reason you want to find last quote date in the file, we recommend that you copy this table data into excel, convert that data range into the table and then order it by date.
• Scripts do not delete data from quotes files, but just append data to the end of the file. So if you run daily updates, you will notice that quotes with the latest day will be at the end of the file. But again, the order of the records is not important.
• There should NOT be any double quotes around any values.
• Each file should have data just for one symbol.
• For each day there should be just one record.
• You can edit the file in any text editor, like notepad. You can adjust quote values or delete duplicate records. You can delete multiple records, but if you do so, keep in mind, that generally, you might want to delete records within the specific period where period ends with last quote date!
• When new quotes are appended, scripts append data just after the latest date of the record already in the file. If you have any missing quote dates and want to re-extract quotes for them, then you need to delete all records that have a date after missing quotes dates and re-run extract script. Example - your quotes file has records up to 2017-09-19, but data for all month 2017-08 is missing. In such case delete all data from 2017-08-01 until 2017-09-19 and re-run extract. After this deletion, extract script will find last quote date in the file as 2017-07-31 and will request data from 2017-08-01 until today’s date.
• Based on the above principle, if you change MinDate parameter value, quotes that are already in the file will not be changed. So if your MinDate was set to earlier date, then older quotes will not be extracted. At the same time, if you MinDate was set to later date, existing quote records will not be deleted. So if you change MinDate, your should backup existing quote file, remove the file from Quotes folder and re-run extract script.
• It is always a good idea to back up your quote files and/or store them on cloud-based (OneDrive, DropBox, GDrive) folder. Sometimes some quote providers will stop supporting some symbols. Other times you will manually edit the file and accidentally change the file format. Or your computer could just crash and you will lose your files. Be prepared for that - always backup ALL data.

Manual quotes files

Any file that has extension .txt and is in Quotes folder OR ANY SUB-FOLDERS will be included into the final PSData\Quotes.csv file! So you can have as many sub-folders and as many quote files inside of the Quotes folder and they all will be used! There is an exception to this rule - if the file name ends with _Archive.txt, then it is assumed that this is archive file and it will not be included into a final PSData\Quotes.csv file, unless you set parameter “IncludeQuoteArchiveFolder” in psConfig.txt to “Yes”. So any file that is inside of Quotes folder OR ANY SUB-FOLDERS must have the same format as it was described above. Keep in mind that archiving script will split each quote file into 2 files inside of any Quotes subfolders too.

It is recommended, that in Quotes folder you just keep symbols that you are currently actively extracting quotes for. For symbols that you do not trade anymore, it is recommended to move their quotes files (including archive file) to some subfolder and remove that symbol from psConfig.txt. For example, you might want to have sub-folder “Manual” where you keep quotes for symbols that you have to manually enter and you might want to have sub-folder “OldQuotes” where you would keep quotes for symbols that you no longer trade.

Intraday quotes

At the same level where you have “Quotes” folder, there also will be “QuotesIntraDay” folder. This folder will have intraday quotes from different sources.

How to specify symbol information for quote extraction

In the psConfig.txt file, there are multiple sections (Yahoo, Google, GoogleWeb, Stooq, AlphaVantage) where you can specify symbols that you want to get quotes for using related scripts. For each symbol, you can also optionally specify a list of parameters that will affect how quotes will be extracted and then presented to Portfolio Slicer.
Here is an order of parameters that can be used when specifying Symbol:

Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend

• Symbol - Symbol/Ticker that is used by that quote source. For example, for Yahoo source, you might use symbol “XIU.TO”, but for Google same symbol would be specified as “TSE:XIU”.
• MinDate - Minimum date (format YYYY-MM-DD) from when a quote will be extracted. Let’s say, your Portfolio Slicer tracks your investments from 2001-12-31, but you started to trade AAPL just from 2015-01-10. As you do not need quotes before 2015-01-10, you would specify a value for this parameter 2015-01-10 and for that symbol, there will be no quotes before that.
• MaxDate - Maximum date (format YYYY-MM-DD) until when a quote will be extracted. Let’s say, you used to trade AAPL symbol before, but sold stock in 2017-01-10. As you do not need quotes after 2017-01-10, you can specify a value for this parameter 2017-01-10 and after that date, quotes will not be extracted.
• IntraDayFlag[Y|N] - Specifies if you want to get intraday quotes from this symbol. If the value is not set or set to Y, then you will get intraday quotes. If you are trading mutual funds, you want to set value for this parameter to N, as usually mutual funds are not priced midday - just at the end of the day.
• [DividendFlag[Y|N] - Specifies if you want to also extract dividend information for this symbol. Currently, this parameter works just with Yahoo section as other sources do not provide dividend payment information. If the value is set to N or not specified, then dividend information will not be extracted.
• FactorHistory - Factor that will be applied to the days closing quotes when preparing quotes.csv file for Portfolio Slicer. Sometimes quotes provided by some sources are in pence and not pounds (cents and not dollars). In such case, you would want to use factor 0.01, so that the quote amount is corrected. This factor is commonly used with UK stocks and Yahoo/Google source.
• FactorIntraDay - Factor that will be applied to intraday quotes when preparing quotes.csv file for Portfolio Slicer. Sometimes quotes provided by some sources are in pence and not pounds (cents and not dollars). In such case, you would want to use factor 0.01, so that the quote amount is corrected. This factor is commonly used with UK stocks and Yahoo/Google source.
• FactorDividend - Factor that will be applied to dividend amounts when preparing dividends.csv file for Portfolio Slicer. Sometimes dividend amounts provided by some sources are in pence and not pounds (cents and not dollars). In such case, you would want to you fact 0.01 so that dividend amount is corrected. This factor is commonly used with UK stocks and Yahoo/Google source.

• AAPL
• AAPL,2015-01-10
• AAPL,,2017-01-10
• AAPL,2015-01-10,2017-01-10
• AAPL,,2017-01-10,Y,Y
• LSE.L,,,,,0.01,0.01

How quotes are updated

Quotes for each symbol can come from different data sources (Yahoo Finance, Google Finance, Stooq, etc.). There are different scripts for different data sources and you can see all the scripts in the “\Scripts” folder. There are usually separate scripts that get each day closing quote (price) and there are other scripts that get intraday quotes. For example, to get quotes from Yahoo Finance website, there are scripts “GetQuotes-Yahoo.ps1” (day closing quote) and “GetQuotes-YahooIntraday.ps1” (intraday quote) and to get quotes from Google there are scripts “GetQuotes-Google.ps1” (day closing quote), “GetQuotes-GoogleWeb.ps1” (day closing quote) and “GetQuotes-GoogleIntraday.ps1” (intraday quote). Each script will read a list of symbols from psConfig.txt file related section and will get quotes data for that set of symbols.
When “day closing quote” scripts are executed, as the first step they will delete related intraday quote file. For example script “GetQuotes-Yahoo.ps1” will delete file “\QuotesIntraDay\YahooIntraday.txt”. This is done so that ever script run will request latest intraday quotes.
After that for each symbol script does following:

• The script will calculate file name where the quotes should be stored.
• The script will check if files for that symbol already exists. If the file is found, then this script will find the date for the latest quote in that file and next quote request date will be that date plus 1 day. If the file does not exist, then next quote request date will be either SymbolMinDate (if specified next to symbol in the corresponding section) or parameters MinDate (from psConfig.txt) value.
• The script will request quotes from calculated next quote date until today.
• The script will append newly received quotes (if any are available) to symbol quote file.

Script logs

If you want to get more details on what happened during script execution, you should check log files in the folder “\Scripts\Log”. For every .ps1 script there will be same name log file with extension .txt. For example, for script GetQuotes-Yahoo.ps1 there will be log file GetQuotes-Yahoo.txt with detail log information. Usually, this log file will contain URL that was sent to the source server to get quotes. Example of the log file content: