How currency exchange 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.

Currency exchange data location

When you execute the script to extract currency exchange data from different sources, that currency exchange data is downloaded from some internet source and it is stored on your computer in the folder "<DataRootFolder>\CurrExch". 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>
In such configuration, your currency exchange data will be stored in the folder "c:\PortfolioSlicer\CurrExch".

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>
In such configuration, your currency exchange data will be stored in the folder "c:\OneDrive\PortfolioSlicer\CurrExch".

Currency exchange file naming

If you are dealing just with one currency, then the currency exchange folder will be empty. If you have more than one currency, then in the currency exchanges folder, you will have 2 currency exchange files for each currency exchange pair with the name “Currency1_Currency2.txt” and “Currency2_Currency1.txt”. So if you are dealing with 2 currencies, then you will have 2 files and if you are dealing with 3 currencies, then you will have 6 files.
For example, if you have symbols with CAD and USD currencies, then you will have files CAD_USD.txt and USD_CAD.txt. If you are dealing with currencies CAD, USD, and EUR, then you will have 6 files: CAD_EUR.txt, CAD_USD.txt, EUR_CAD.txt, EUR_USD.txt, USD_CAD.txt and USD_EUR.txt.

Currency exchanges file content

Currency exchange file contains multiple lines where each line represents currency exchange value for the specified date. Each line in this file has 4 values separated by a comma:

  1. The first value is currency exchange date in the format YYYY-MM-DD. Example: 2017-09-16.
  2. The second value is currency exchange amount. 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 “currency from”. Example: CAD
  4. The fourth value is a “currency to”. Example: USD
Here is an example of currency exchange file records:

Here are important rules about currency exchange files:

  • Currency exchange 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 currency exchange date in the file, we recommend that you copy this table data into excel, convert that data range into table and then order it by date.
  • Scripts do not delete data from currency exchanges files, but just append data to the end of the file. So if you run daily updates, you will notice that currency exchanges with the latest day will be at the end of the file. But again, 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 currency pair.
  • For each day there should be just one record.
  • You can edit the file in any text editor, like notepad. You can adjust currency exchange 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 specific period where period ends with last currency exchange date!
  • When new currency exchange data is appended, scripts append data just after latest date of the record already in the file. If you have any missing currency exchange dates and want to re-extract currency exchanges for them, then you need to delete all records that have a date after missing currency exchange dates and re-run extract script. Example - your currency exchanges 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 currency exchange date in 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, currency exchanges that are already in the file will not be changed. So if your MinDate was set to earlier date, then older currency exchanges will not be extracted. At the same time, if you MinDate was set to later date, existing currency exchange records will not be deleted. So if you change MinDate, your should backup existing currency exchange file, remove the file from currency exchanges folder and re-run extract script.
  • It is always a good idea to back up your currency exchange files and/or store them on cloud-based (OneDrive, DropBox, GDrive) folder. Sometimes some currency exchange providers will stop supporting some currencies. 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 currency exchange files

Any file that has extension .txt and is in currency exchange folder OR “\CurrExch\Manual” subfolder will be included into a final \PSData\CurrencyConv.csv file! It is recommended, that in currency exchanges folder you just keep currencies that you are currently actively extracting currency exchanges for. For currencies that you do not trade anymore, it is recommended to move their currency exchange files to “\Manual” subfolder and remove that symbol from psConfig.txt or change parameter not to get currency exchanges for that currency.

If you are planning to manually add currency exchange payment information, create a file for each currency pair in a \CurrExch\Manual folder and add one line per each exchange as per the example above.

How to specify symbol information for currency exchange extraction

In the psConfig.txt file, there is just one section “Currency” where you can list currencies that you are working with. Normally, each currency has 3 letter, but that might depend on currency exchange rate source. That is normally USA currency should be specified as USD, but if your source provider uses code US$, then everywhere (psConfig.txt and Excel file) you would need to use value US$.

How currency exchanges data is updated

Currency exchanges can come from different sources and each source has a separate script, example: “Scripts\GetExchRates-BoC.ps1”, “Scripts\GetExchRates-ECB.ps1”, “Scripts\GetExchRates-Stooq.ps1” and “Scripts\GetExchRates-YahooIntraday.ps1”. When these scripts are executed, as the first step they will delete all currency intraday data and then will do following:

  • Script will calculate file name where the currency exchanges for that pair should be stored, example: CAD_USD.txt.
  • Script will check if currency exchange file for that currency pair already exists. If the file is found, the script will find a date for the latest currency exchange in that file and the next currency exchange request date will be that date plus 1 day. If file does not exist, then next currency exchange request date will be MinDate value in psConfig.txt file
  • Script will request currency exchanges from calculated next currency exchange date until today.
  • Script will append newly received currency exchanges (if any are available) to symbol currency exchange file.

Even there are multiple currency exchange extract scripts, they will work with exactly the same files. So theoretically you could on one day extract data using one data source and change data source another day. This would work without problem, but you just have to remember that each data source might use a different method. For example, ECB (European Central Bank) source might provide exchange values at the end of the Europen market close and BoC (Bank of Canada) source might provide data for the end of a day of the Canadian market close. Because there is 5-7 hour time difference, these values could be slightly different.

Intraday currency exchange data

At the same level where you have “CurrExch” folder, there also will be “CurrExchIntraDay” folder. This folder will have intraday currency exchange data from different sources. At the point of writing this page, there was just one script that was able to extract intraday currency exchange data: “\Scripts\GetExchRates-YahooIntraday.ps1” and this script was adding create currency exchange data into a file “\CurrExchIntraDay\YahooCurrExchIntraday.txt”. Each file in this folder will have ALL intraday quotes from that source. Here is an example of what Google intraday quotes can look like

Data for intraday exchange rates are not placed into CurrExch folder because these value most likely will change over the day, so they are deleted before every new currency exchange request. If you have any unexplained issues, you can always delete all .txt files from this “\CurrExchIntraDayIntraDay” folder and re-run extract.

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 GetExchRates-ECB.ps1 there will be log file GetExchRates-ECB.txt with detail log information. Usually this log file will contain URL that was send to source server to get currency exchange data. Example of the log file content: