File - Quotes.csv

To calculate your investment value at any point in time Portfolio Slicer needs to know for how much each share/unit of your investment was selling at any point in time. All that information should be in the file Quotes.csv.

• First line in this files should be header row with text "Date Close Symbol" (without quotes, TAB instead of space).
• Starting from the second row, each row should contain TAB separated values for quote date, price at the end of the day and symbol of that quote.
• "Date" value in each row should have format YYYY-MM-DD. So length of each value for "Date" column should be exactly 10 characters.
• "Close" value in each row should be decimal number where integer and fractional part is separated by character dot "." and not comma as it is custom in many European countries. This value should not have any thousand separators.
• "Symbol" column should have value that is listed in the Symbol table.
• This file should have just one row for each Date+Symbol combination - that is this file cannot have any duplicate quotes (record with the same symbol on the same day).
• Minimum date in this file should be equal or after "MinDate" parameter in Config table.
• Maximum date in this file should be equal or before todays date. Normally this should be the date for last business day.

Portfolio Slicer has report few reports in "Daily" worksheet that shows capital gain or loss for last 30 business days. These few reports expects that Quotes.csv file will have daily quote for the last 30 business days. All other reports will work if quotes will be provided daily or monthly. So for better performance we recommend that Quotes.csv file would have daily quotes for last 30 business days and then monthly quotes for dates before.

Generated Quotes

You might have some investments that have static price. Example of this could be GIC (Guaranteed Investment Certificate) or some other asset that has fixed price. This investment might or might not pay some dividend or interest. To track such investment Portfolio Slicer needs to receive price updates at least once a month. If such price is not provided in Quotes.csv file, Portfolio Slicer will assume that price is 0 and that will make total value of that investments as 0.

Data for Quotes.csv file

Some Portfolio Slicer users subscribe to service that provides Quotes data to them. These users will be able to use tools from their data provider to build Quotes.csv file as per requirements described above. Portfolio Slicer comes with additional scripts that will be able to add static quotes to your quotes and also check if data in your file meets requirements.

Other Portfolio Slicer users can download quotes available from different financial websites. Most popular websites where you can download financial data is "Yahoo Finance" and "Google Finance".

Quotes from Yahoo Finance

Yahoo Finance is most popular website that provides users with quotes from many different stock markets. To get a quotes for specific symbol you should enter Symbol/Ticker or Symbol name into “Quote Lookup” box, click on the “Go” button and you will be redirected to window with current price and other detail information of that symbol. From that window you can choose “Historical Prices” link where you can request to download quotes for any selected period.

At the bottom of that page you will see a link “Download to Spreadsheet” that will download quotes for your selected period.

If “Historical Prices” window shows no historical quotes, that means that you cannot get quote prices from Yahoo Finance website and you should be looking for other sources.

Yahoo Finance should be your first choice when you are looking for quotes. List of available tickers for Yahoo Finance website is available on this InvestExcel.net page. This website also has page with information about various web services to download quote data.

Please note that non USA symbol from Yahoo Finance website will have suffix appended to the end of the ticker. For example, stock for company "Toronto Dominion Bank" that is traded in New York, USA in USD currency will have symbol "TD", but the same company that is traded in Toronto, Canada in CAD currency will have symbol "TD.TO". Company "APC Technology Group" that is traded in London will have a symbol "APC.L. As you will see - each stock market has different suffix - Toronto has ".TO" or ".V", London has ".L", China has ".SZ", Bombay has ".BO", etc).

If you quote file has symbols with suffix, then you Symbol table will have to define symbol the same way and in Transactions table you will also have to use symbol with suffix (or use Symbol Alias table).

Known issues: For some symbols (most often in UK), Yahoo Finance might have quotes in pence and not Pound. In such case you will have to convert data to Pounds, otherwise Portfolio Slicer results will not be right.

Preparing Quotes.csv file

We recommend that on your PC you create folder "\Quotes" where you will keep each symbol quotes in separate file. For simplicity do not have header row in each quotes file - just keep data rows. Name each file <symbol>.txt, but for <symbol> part replace actual Symbol special characters like "^", ":" with "_" (example: aapl.txt, tse_to.txt, ...) .

Download sample Quotes.csv file here.