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!

Comments

Sign In or Register to comment.