Scripts for External Data files

MaximT

Hello,

First of all thank you for finally releasing "local" version of Portfolio Slicer. I see that you have not included any scripts to get data from Yahoo/Google financial sites. As you already have PowerShell scripts for for maintenance, you can easily write script to get data from Yahoo Finance website. Here is example:

cls;
$minDate = "2016-01-01";
$yaSymbolList = "MSFT, AAPL,TD.TO,XRE.TO";

$urlBase = "http://ichart.finance.yahoo.com/table.csv?s=@@Symbol@@&a=@@STARTMTH@@&b=@@STARTDAY@@&c=@@STARTYEAR@@&g=d&ignore=.csv";
$scriptPath = Split-Path -parent $MyInvocation.MyCommand.Definition; if (!(Test-Path -Path ($scriptPath+"\Quotes"))) {New-Item ($scriptPath+"\Quotes") -type directory};
$quoteIDFileYA = $scriptPath+"\Quotes\YahooIntraday.txt"; if (Test-Path $quoteIDFileYA) { Remove-Item $quoteIDFileYA;}

ForEach ($s in @($yaSymbolList.split(","))) {
$symbol = $s.Trim();$symbolQuoteFile = $scriptPath + "\Quotes\_" + $symbol.Replace(":","_").Replace("^","_").Replace("&","_") + "_" + ".txt"; $lastDateInFile="";
if (Test-Path -Path $symbolQuoteFile) {$fc = @(import-csv $symbolQuoteFile -Header "Date","Close","Symbol" | Sort-Object -Property "Date"); if ($fc.count -gt 0) {$lastDateInFile = $fc[$fc.count-1].Date;} }
if ($lastDateInFile -ne "") {$nextDate = ([datetime]::ParseExact($lastDateInFile,”yyyy-MM-dd”,$null)).AddDays(1).ToString("yyyy-MM-dd"); } else {$nextDate = $minDate;}
$year = $nextDate.Substring(0,4); $month = [int]$nextDate.Substring(5,2) - 1; $day = $nextDate.Substring(8,2);
@Symbol@@STARTMTH@@STARTDAY@@STARTYEAR@@",$year);
$wc = new-object system.net.WebClient; try {$webpage = $wc.DownloadData($url);} catch { "Symbol $symbol no data"; continue;}
$quotesTxt = [System.Text.Encoding]::ASCII.GetString($webpage); if ($quotesTxt.length -le 45 -or $quotesTxt.Contains("")) {"$symbol- No new data received."; continue;}
$ql = $quotesTxt.Split("`n");
$ql | ? {$_.trim() -ne "" -and ($_.StartsWith("1") -or $_.StartsWith("2"))} | %{$a=$_.Split(","); $a[0]+","+$a[4]+","+$symbol; } | Out-File $symbolQuoteFile -Encoding OEM -Append;
"Symbol $symbol done. New record count: " + $ql.Count;
}

MaximT

And here is example on how to get data from Google finance website:

cls;
$minDate = "2016-01-01";
$goSymbolList = "MSFT,AAPL";

# http://www.google.com/finance/historical?q=NYSEARCA:PWC&startdate=Jan+11,+2014&output=csv
$urlBase = "http://www.google.com/finance/historical?q=@@Symbol@@&startdate=@@StartDate@@&output=csv"; $mths = @("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
$scriptPath = Split-Path -parent $MyInvocation.MyCommand.Definition; if (!(Test-Path -Path ($scriptPath+"\Quotes"))) {New-Item ($scriptPath+"\Quotes") -type directory};

ForEach ($s in @($goSymbolList.split(","))) {
$symbol = $s.Trim();$symbolQuoteFile = $scriptPath + "\Quotes\_" + $symbol.Replace(":","_").Replace("^","_").Replace("&","_") + "_" + ".txt"; $lastDateInFile="";
if (Test-Path -Path $symbolQuoteFile) {$fc = @(import-csv $symbolQuoteFile -Header "Date","Close","Symbol" | Sort-Object -Property "Date"); if ($fc.count -gt 0) {$lastDateInFile = $fc[$fc.count-1].Date;} }
if ($lastDateInFile -ne "") {$nextDate = ([datetime]::ParseExact($lastDateInFile,”yyyy-MM-dd”,$null)).AddDays(1).ToString("yyyy-MM-dd"); } else {$nextDate = $minDate;}

$year = $nextDate.Substring(0,4); $month = [int]$nextDate.Substring(5,2) - 1; $day = $nextDate.Substring(8,2); $startDate=$mths[$month]+"+"+[int] $day+"%2C+"+$year; # Jan+1%2C+2015
@Symbol@@StartDate@@",$startDate);

$wc = new-object system.net.WebClient; try {$webpage = $wc.DownloadData($url);} catch { "Symbol $symbol no data"; continue;}
$quotesTxt = ([System.Text.Encoding]::ASCII.GetString($webpage)).Replace("???",""); if ($quotesTxt.length -le 45 -or $quotesTxt.Contains("")) {"$symbol - No new data received."; continue;}
$ql = $quotesTxt.Split("`n");
ForEach($q in $ql) { # for each quote line
if ($q.trim() -ne "" -and !$q.StartsWith("Date")) { #ignore quote lines without data
$qParts = $q.Split(","); $qDate=$qParts[0]; $qPrice = $qParts[4].REPLACE("-","0"); if ($qDate.Length -eq 8) {$qDate="0"+$qDate}; # quote date adding leading zero if required 5/06/2015 -> 05/06/2015
$mthName = $qDate.Substring(3,3); $qMonth= "0"+($mths.IndexOf($mthName) +1); $qMonth = $qMonth.Substring($qMonth.length-2,2); # converting month name into number: Feb->02
if($qDate.Substring(7,2) -le "50") {$qYear="20"+$qDate.Substring(7,2)} else {$qYear="19"+$qDate.Substring(7,2)} # converting 2char year into 4 char year YY->YYYY (adding century)
$qDateFormatted = $qYear + "-" + $qMonth +"-" + $qDate.Substring(0,2);
if($qDateFormatted -ge $nextDate) { $qDateFormatted + "," + $qPrice + "," + $symbol | Out-File $symbolQuoteFile -Encoding OEM -Append;}
}
}
"Symbol $symbol done. New record count: " + $ql.Count;
}

Could you add such scripts to your release?

VidasMatelis

Hi Maxim,

Is there any chance that you could be able to write such scripts adjusted for Portfolio Slicer requirements? As per documentation, we need external data file: ”CurrencyConv.csv“ ,”Quotes.csv“ ,”Dividends.csv”.
If possible, could you extend psConfig.txt file to include parameters for Yahoo/Google?

TIA!

MaximT

Vidas,

I'll see what I can do. I'll check your new PS version and if all looks good, I'll migrate my very old PS workbook to this new release As I'll need data, I will adjust my existing scripts, so I'll share them here. Give me few days, maybe a week.

VidasMatelis

Thank you!

MaximT

Hi Vidas,

I created PowerShell scripts that will get data from websites. But I cannot find a way to attach them to this post. I see link "Attach image/file", but when I click on it, it does not work.

VidasMatelis

Hi MaximT,

I see your problem, but I am not sure how to fix this now. Could you please save files on DropBox or OneDrive or GDrive and share them through these services and post link here? Also, could you start new thread, if scripts are working, I'll make thread "sticky" as this would be very helpful scripts to many users.

TIA!

MaximT

Hi Vidas,

I posted new thread with link to DropBox file here: http://new.portfolioslicer.com/support/index.php?p=/discussion/5/scripts-to-get-data-for-portfolio-slicer-from-yahoo-google-and-boc?new=1

Please let me know if you have any issues with this script.

Maxim.