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.
Requirements for this file are:
- 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.
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.Portfolio Slicer comes with PowerShell script that can generated monthly prices for such investments and append them to your created Quotes.csv file.
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.
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.
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.
Quotes from Google Finance
Google Finance is another popular website with many quote available. Most of the information on this website overlaps Yahoo Finance website, but it also has many quotes that are not available on Yahoo Finance website. For example, if you are looking for Canadian Mutual Fund quotes, it is more likely you will find that information on Google Finance website and not on Yahoo Finance website. To find Google symbol for your ticker start entering either ticker or symbol name into search box on Google Finance website. As you type, you will see list of options appear for your selection.
Then when you will make your selection, you will see symbol code used by Google Finance website:
If you are using Google Finance as your quote source, then you will have to use this Symbol (this example: "MUTF_CA:TDB161") in Symbol table definition.
If you will click on "Historical Prices" you will be able to see quotes for that symbol. For many symbol at the right side there will be a link "Download to spreadsheet" that will allow you to download quotes to a file on your PC.
If you do not see such link, then you will need to copy data from page and paste it into Excel workbook.
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.