PowerShell script to archive old quotes

Portfolio Slicer expects that largest table it will work with will be data from Quotes.csv file. As most financial data utilities/scripts work with daily quotes, for any investor this file can easily have tens of thousands of records. With larger data sets we saw that Portfolio Slicer uses more memory (often more than 32bit Excel can handle), so we felt that it is important to include script that for many Portfolio Slicer users can substantially reduce number of records in Quotes.csv file without visible impact on created reports.

Portfolio Slicer has few reports that need daily data for last 30 business days. These reports are located in "Daily" worksheet. All other reports need just monthly quotes. Because of this Quotes.csv file should have daily quotes for last 30 business day (approximately last 50 calendar days) and monthly quotes for all dates before that.

While building Portfolio Slicer we saw users store investment quotes data in 3 different setups:

  • Some users use tools to download quotes from their data provider into separate files by date. So each day they would get new file with name "quotes_20160204.txt", "quotes_20160205.txt", etc. All these files would go into one folder, or into monthly/yearly folder. In such setup this script will not work. If you have daily quote files and will choose to archive quotes, you will have to manually move some day files out of your folders or ignore these files when creating Quotes.csv file.
  • Some users create "Quotes.csv" file manually by adding quotes on irregular schedule. In such case this script will not work too.
  • Most common scenario we saw - when users have folder with separate file for each symbol with quote data inside. This script is created exactly for such scenario.

This script requires that you have separate file with quotes for each symbol. This file should have no headers, and each row should have 3 comma separated values: Date (format YYYY-MM-DD), Close Price (North American format with "." as separator) and symbol. Order of records in this file is not important - this script will do ordering by date while reading data. This script leave last 50 calendar days in the file and then will leave one quote for each month. All other data will be moved to file in the same folder with the same name and additional suffix _Archive. So if you have file AAPL.txt, then some quotes will stay in that file and not required quotes will be moved into file AAPL_Archive.txt.

This script is not enabled by default and again, is included here because we felt that it could benefit many Portfolio Slicer users who store their quote data in separate files by symbol.