Scripts to get data for Portfolio Slicer from Yahoo, Google and BoC

Comments

• VidasMatelis

  March 2016 edited March 2016

  Thanks Maxim,
  
  I tested scripts and they work very well. I will be using them from now on - as they do just what I want. I had to add my manual quotes for symbols that do not have quotes anymore (like DELL). I hope you do not mind, but I will use your scripts as example on how to get data - they will be part of setup documentation - I just now need to adjust documentation. I will also save copy of scripts on my server for easier download. Let me know if you have any issues with that.
  
• bgo82

  March 2016

  Hello!
  
  I use 3 Currencies for my investments (EUR, GBP, USD) but it looks like the data Download for GBP->EUR does not work.
  
  GetExchRatesBoC.txt:
  =======================================================================================
  20:24:57 --- Starting script GetExchRatesBoC.ps1. Symbol count: 3. MinDate: 2012-03-23
  Found rate file. Looking for quotes starting from: 2016-03-25
  Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_EUROCAE01&lP=lookup_currency_converter.php&sTF=to&sT=_1201&co=1.00&dF=2016-03-25&dT=2016-03-25
  Done: EUR->GBP (from 2016-03-25 to 2016-03-25). New record count: 0.
  Found rate file. Looking for quotes starting from: 2016-03-25
  Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_EUROCAE01&lP=lookup_currency_converter.php&sTF=to&sT=_0101&co=1.00&dF=2016-03-25&dT=2016-03-25
  Done: EUR->USD (from 2016-03-25 to 2016-03-25). New record count: 0.
  Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_IEXE1201&lP=lookup_currency_converter.php&sTF=to&sT=_EUROCAE01&co=1.00&dF=2012-03-23&dT=2016-03-25
  No new data (returned html)
  Found rate file. Looking for quotes starting from: 2016-03-25
  Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_IEXE1201&lP=lookup_currency_converter.php&sTF=to&sT=_0101&co=1.00&dF=2016-03-25&dT=2016-03-25
  Done: GBP->USD (from 2016-03-25 to 2016-03-25). New record count: 0.
  Found rate file. Looking for quotes starting from: 2016-03-25
  Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_IEXE0101&lP=lookup_currency_converter.php&sTF=to&sT=L_EUROCAE01&co=1.00&dF=2016-03-25&dT=2016-03-25
  Done: USD->EUR (from 2016-03-25 to 2016-03-25). New record count: 0.
  Found rate file. Looking for quotes starting from: 2016-03-25
  Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_IEXE0101&lP=lookup_currency_converter.php&sTF=to&sT=_1201&co=1.00&dF=2016-03-25&dT=2016-03-25
  Done: USD->GBP (from 2016-03-25 to 2016-03-25). New record count: 0.
  20:25:02 --- Finished. Quotes Requested/Succeed/Failed: 6/5/1. New records: 0. Duration: 5 sec.
  =======================================================================================
  
  If i try the link bankofcanada.ca/stats/results/csv?sF=LOOKUPS_IEXE1201&lP=lookup_currency_converter.php&sTF=to&sT=_EUROCAE01&co=1.00&dF=2012-03-23&dT=2016-03-25 in the browser an error message appears.
• VidasMatelis

  March 2016 edited March 2016

  MaximT,
  
  Another user privately reported to me:
  1. PowerShell scripts are not consistency encoded - some scripts have ANSI encoding, other UTF-8.
  2. In my scripts I introduced problem with code ParseExact($lastDateInFile,”yyyy-MM-dd”,$null). If you use advanced editor, then you would see that double quotes are different from standard double quotes. That cause some problems if you open scripts in advanced editors (double quotes are replaced by some "garbled" 2 characters. Your scripts have same problem, as you probably copied that part from my scripts.
  3. When open file "AppendGeneratedQuotes.ps1" in its original encoding "ANSI" by Windows notepad or Windows Powershell ISE, the "dd",$null" will display as "dd?$null", which has no comma and isn't correct. This file was created by me, but you might have similar issues too.
  
  Could you please look into this? I am cleaning up my code. I will be converting all scripts to UTF-8.
  
• MaximT

  March 2016

  Hi,
  
  I fixed issue with exchange rate download and saved all scripts as UTF-8. Same link to download.
• VidasMatelis

  March 2016

  I just fixed my scripts to and re-published download zip files. These include Maxim T files as well. So BoC download issue also fixed.
• VidasMatelis

  April 2016

  Maxim,
  
  There are few more issues that were identified over last few weeks, could you please help with those too?
  1. Remove limit for number of currencies - currently limit is 3.
  2. When there is just one currency, script GetExchRatesYahooIntraday generates error.
  3. Some international users have problems loading in decimal numbers. If they computer has setting for "Decimal Symbol" as comma and not dot (for example users in Netherlands) and "Digit grouping" (thousand separator) symbol as dot , then number 28.12 is loaded as 2812. Can you add parameter that specifies "Decimal Symbol" with default value as "." (dot). To support comma as decimal symbol, final files you generate should have different filed separator - maybe "|" or ";" or tab (char(9)) ?
  
  Thanks!
• MaximT

  April 2016

  Hey Vidas,
  
  I updated scripts.
  I added new parameters:
  ColumnSeparator (values Comma, Tab, VerticalBar), default Tab
  DecimalSeparator with default value of .
  Other issues were fixed too.
  
  Link to updated scripts is here.
  
  I need another version of Portfolio Slicer that will work with these settings, currently with these scripts I cannot refresh V2.1.
  
  
  Enjoy,
  
• VidasMatelis

  May 2016

  Maxim,
  
  Because of the way Excel+PowerPivot works, can you please adjust script that would create file in c:\PortfolioSlicer\PSData folder with static content:

  
  [CurrencyConv.csv]
  Format=TabDelimited
  
  [Dates.csv]
  Format=TabDelimited
  
  [Dividends.csv]
  Format=TabDelimited
  
  [Quotes.csv]
  Format=TabDelimited
  
  

  Just some users need it, but it makes sense to have it for everyone.
  This file is needed so that Excel+PowerPivot knows that data to be loaded has TAB separated values.
  
  TIA!!!!!
• MaximT

  May 2016 edited May 2016

  Hi Vidas,
  
  Can you add following code to MakePSDataFiles.ps1 to the very end:

  
  # #######################################################################################
  # ######################## Creating file schema.ini if column separator is TAB
  # #######################################################################################
  if ($colSepTxt.ToLower() -eq "tab") {
      $schemaFile = "[CurrencyConv.csv]`r`nFormat=TabDelimited`r`n`r`n[Dates.csv]`r`nFormat=TabDelimited`r`n`r`n[Dividends.csv]`r`nFormat=TabDelimited`r`n`r`n[Quotes.csv]`r`nFormat=TabDelimited`r`n";
      $schemaFile | Out-file ($psDataFolder+"\schema.ini") -Encoding OEM;
  }
  

  This will do what you want when psConfig.txt is configured to use tab as column delimiter. I will update my release later.
• MaximT

  May 2017

  There is updated script for currency exchange rate download, more info here: http://support.portfolioslicer.com/index.php?p=/discussion/181/exchange-rate-issues-from-apr-28-2017#latest
  
• VidasMatelis

  May 2017

  Maxim,
  
  Yahoo FInance stopped providing historical quotes, so Yahoo is not a good source for quotes. How hard it would be for you to create a script to get Intraday quotes for Google?
• MaximT

  May 2017

  Vidas,
  
  I have script to get Google midday quotes: https://www.dropbox.com/s/81gjawjimm2uicf/GetQuotesGoogleIntraday.zip
  Un-block, unzip, copy to scripts folder and then add it to UpdatePSDataIntraday.bat file.
• MaximT

  September 2017

  Vidas,
  
  Here are updated scripts: https://www.dropbox.com/s/7cgg715hlkq07qu/Scripts20170906.zip?dl=1
  
  Yahoo recently changed how data can be requested - it now requires cookie and special crumb value for each request. I updated Yahoo scripts and they all should be working, including historical quotes, intraday quotes and dividends.
  I also added script (GetExchRatesECB.ps1) to get Exchange rates from European Central Bank.
  There are no changes to Google or BoC scripts, but I included them so to better keep track of them.
  
  HTH,
• maffs0

  September 2017

  Thanks MaximT. I've been using Google for both intraday and end of day prices, but since 5 September the historical prices haven't been updating and no longer appear as a link on the main page of the stock. I'm in the UK, but I checked and can't find the historical page for big US stocks either.
  
  This is a nightmare for me because I have spent hours getting my spreadsheet and config file right. I have a few problems:
  1. the set of scripts I have don't have a Yahoo update script. I have seen the new one you put up yesterday, so can download that.
  2. My config file doesn't have a place to put Yahoo prices. Can I just replace my current config file with the one from your 2.2+ one at the top of this page?
  3. My biggest problem. If I move from Google to Yahoo, all the symbols change from LON:XXX to XXX.L. This will affect my trans page and symbols page on the spreadsheet and also the config file as I can't just copy and paste from the google list to the Yahoo list.
  
  I hope all this makes sense. I've been loving PS and the scripts and would really appreciate your thoughts on how best to solve these three problems.
  
  Thanks,
  maffs.
• VidasMatelis

  September 2017 edited September 2017

  Maffs,
  
  1. Please re-download latest scripts from here: http://www.portfolioslicer.com/download.html
  2.
  
  To include Yahoo, to your psConfig.txt add section at the bottom, before SymbolRename section :

  # Yahoo: list of symbols from Yahoo Finance website (link to download csv file available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
  
  
  

  3. Use SymbolRename section in psConfig.txt file. If you have symbols in Excel described as LON:ABC, then:
  - from quotes folder delete (backup to safe location) LON_ABC.txt and LON_ABC_Archive.txt files
  - remove symbol LON:ABC from google section
  - add symbol ABC.L to Yahoo section
  - To SYmbol rename section add string:
  ABC.L,LON:ABC
  
  This rename will make sure that all quotes prepared for Excel will have rename dane: ABC.L -> LON:ABC