Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

edited April 2016 in External Data Files
Link to scripts for Portfolio Slicer v2.1 here.
Link to scripts for Portfolio Slicer v2.2+ here.
------------------------------------------------------------------------


I created scripts that fully integrate with existing Portfolio Slicer scripts:
- I added more parameters to psConfig.txt file
- I added more PowerShell scripts to get data from Yahoo, Google, Bank of Canada and Ariva
- I updated your batch file to execute these additional scripts

Install
1. download files from here: https://www.dropbox.com/s/fv2dpwzo7nb8sgg/PSScriptsByMaxim.zip?dl=0
2. unblock zip file (as per Portfolio Slicer guide) and unzip to temporary folder
3. copy files from Scripts folder to Portfolio Slicer Scripts folder and copy 2 batch file to folder above (where existing batch file resides)
4. update Scripts/psConfig.txt file with your currency and symbol list
5. run UpdatePSData.bat file to get historical and intra-day data. Or if you already run today that bat, then same day you can request just intra-day quotes by running UpdatePSDataIntraday.bat

How it works
After running script in "DataRootFolder" you will see 4 folders:
- CurrExch. Here script will "permanently" store currency exchange files. When script runs it first check up to when exchange rates are already stored and then request just new exchange rates
- Dividends. Here script will "permanently" store Dividend payment information - one file per symbol
- Quotes. Here script will "permanently" store quotes. When script runs, it first checks up to when quotes were already downloaded and will request just new quotes
- QuotesIntraDay. Here script will temporary store intra-day quotes. These quotes will be replaced during each load

Then script MakePSDataFiles.ps1 will read files from these data folders and will create 4 files that are used by Portfolio Slicer. This script includes all subfolders from these data folders - I need that because I keep files with quotes that are not available anymore in separate subfolder under Quotes folder. After that original Portfolio Slicer script will check these files.

You should keep in mind that if you ever want to change MinDate parameter value, you must delete all data you already extracted and re-run scripts again.

Parameters in psConfig.txt file.
- <Currency>: Here list currencies that you want to get exchange rates for. Supported up to maximum 3 different currencies.
- <Yahoo> - list of symbols to get from Yahoo Finance website.
- <Google> - list of symbols to get from Google Finance website. You can list symbols here JUST WHEN in "Historical Prices" page on the right under the chart you see option "Export"-"Download to spreadsheet". If you do not see this option, use parameter.
- <GoogleWeb> - list of symbols to get from Google Finance website. Here include just symbols that do not have "Export"-"Download to spreadsheet" link in "Historical Prices" page (on the right under chart)
- <Ariva> - list of symbols for quotes from Ariva.de website (mostly Europe). SymbolCode can be found on ariva website in symbol charts area in the link under "Chart (L&S) fur Ihre Website?". That link will be like this: www.ariva.de/chart/export_chart?secu=684&boerse_id=16&t=3years, where code after "secu=" specifies Ariva Symbol Code.
- <IncludeQuoteArchiveFolder> - Portfolio slicer has script to archive quotes. I enabled archiving for my setup (runs faster), but sometimes I need to have daily quotes for last few years. In such case I set parameter value to "Yes" and run batch and newly created Quotes.csv file then has daily quotes for all years.

For Yahoo and Google quotes you should specify symbol information in the format:
Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
Symbol - Symbol
MinDate - from when you want to get quotes, format YYYY-MM-DD. If not specified, then use MinDate parameter
MaxDate - up to when you want to get quotes, format YYYY-MM-DD. If not specified, then this means up to current day
IntraDayFlag - values Y or N. If Y, then you will also attempt to get mid day quotes (that data is not stored permanently)
DividendFlag - values Y or N. If Y, then you will also attempt to get data about dividend payments. Default value (if not specified) is N.
FactorHistory - factor that you will use to multiply historical quote price. Some London quotes come in pence not pound, so you need to use factor 0.01
FactorIntraDay - factor that you will use to multiply intraday quote price. Some London quotes come in pence not pound, so you need to use factor 0.01
FactorDividend - factor that you will use to multiply dividned amount. Some London dividend amounts come in pence not pound, so you need to use factor 0.01
Just Symbol in mandatory - other parameters you can skip or have empty value, example:
AAPL
AAPL,,2015-01-1
AAPL,,,Y,Y

Dividends
Currently these scripts can get Dividends information just from Yahoo Finance website - I did not do that for Google.
To get Dividends you need to specify 5th parameter value as Y, example:
AAPL,,,,Y
or
AAPL,,2015-01-01,,Y

Example
psConfig.txt new parameters with examples:
<Currency>
USD
CAD
</Currency>

# Yahoo: list of symbols from Yahoo Finance website. Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
<Yahoo>
AAPL,2008-12-31,2011-09-30,Y,N,0.99,,
^GSPTSE
^GSPC
</Yahoo>

# Google: list of symbols from Google Finance website (link to download csv file available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
<Google>
C,2011-01-10,2015-01-29,Y,N,,,
</Google>

# GoogleWeb: list of symbols from Google Finance website (link to download csv file not available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
<GoogleWeb>
TSE:DLR,2014-05-01,2014-05-31,Y,N,,,
MUTF_CA:TDB900
</GoogleWeb>

# Ariva: list of symbols from Ariva website. Format: Symbol, SymbolCode, MinDate, MaxDate,FactorHistory
<Ariva>
CH0012005267,684
</Ariva>

# IncludeQuoteArchiveFolder: Values Yes or No. If value is Yes, then when creating Quotes.csv file will include files with name patter _Archive.txt, otherwise these files will be excluded
<IncludeQuoteArchiveFolder>
No
<IncludeQuoteArchiveFolder>

With these scripts most of the Portfolio Slicer users will be able to easily create files required. By the way, I use windows scheduler to run these scripts (late night and early morning full script and then hourly intra-day script).

Please report any issues here.

Enjoy!
«1

Comments

  • 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.
  • 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.
  • 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.
  • Hi,

    I fixed issue with exchange rate download and saved all scripts as UTF-8. Same link to download.
  • 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.
  • 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!
  • 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,
  • 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!!!!!
  • 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.
  • 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
  • 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?
  • 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.
  • 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,
  • 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.
  • edited September 7
    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
    <Yahoo>
    </Yahoo>
    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

Sign In or Register to comment.