# ######################################################
# 2018-Sep-07. Created by Maxim T. 
# URL: https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip returns zip file with exchange rates from EUR to specified in the header currency. First line in the file is header with Date and list of currency, from second line for each date there is exchange rate value from EUR to that currency
# File content example (first 3 lines): 
# Date,USD,JPY,BGN,CYP,CZK,DKK,EEK,GBP,HUF,LTL,LVL,MTL,PLN,ROL,RON,SEK,SIT,SKK,CHF,ISK,NOK,HRK,RUB,TRL,TRY,AUD,BRL,CAD,CNY,HKD,IDR,INR,KRW,MXN,MYR,NZD,PHP,SGD,THB,ZAR,ILS,
# 2017-08-04,1.1868,130.67,1.9558,N/A,26.068,7.4388,N/A,0.9028,304.36,N/A,N/A,N/A,4.241,N/A,4.5635,9.6053,N/A,N/A,1.1494,N/A,9.3618,7.407,71.4691,N/A,4.1903,1.4888,3.6911,1.492,7.9757,9.2782,15791.56,75.5035,1334.12,21.1344,5.0777,1.5928,59.588,1.6097,39.455,15.8267,4.2939,
# 2017-08-03,1.186,130.91,1.9558,N/A,25.965,7.4381,N/A,0.90318,303.74,N/A,N/A,N/A,4.2526,N/A,4.5655,9.6093,N/A,N/A,1.1501,N/A,9.3768,7.4063,71.6789,N/A,4.1992,1.4943,3.7017,1.4951,7.9726,9.2724,15804.04,75.521,1336.54,21.1435,5.078,1.5986,59.625,1.613,39.429,15.7628,4.2501,
#
# To Convert from EUR to CAD we find the exchRate for that date ($exchRate1) and divided it by 1.0: $exchRate = $exchRate1 / 1.0.       Example: 2017-08-04: $exchRate1 = 1.492, : $exchRate = 1.492 / 1.0 = 1.492.
# To Convert from CAD to EUR we find the exchRate for that date and divide 1.0 by that rate ($exchRate1): $exchRate = 1.0 / $exchRate1. Example: 2017-08-04: $exchRate1 = 1.492, : $exchRate = 1.0 / 1.492 = 0.670241.
# To Convert from CAD to USD we find exchange rates $exchRate1 and $exchRate2 for that date and use formula: $exchRate = $exchRate2/$exchRate1. Example: 2017-08-04: (EUR->CAD) $exchRate1 = 1.492, (EUR->USD) $exchRate2 = 1.1868, (CAD->USD) $exchRate = 1.1868/1.492 = 0.795442
#
# ######################################################
$scriptPath = Split-Path -parent $MyInvocation.MyCommand.Definition; 
. ($scriptPath + "\psFunctions.ps1");     # Adding script with reusable functions
. ($scriptPath + "\psSetVariables.ps1");  # Adding script to assign values to common variables

$logFile        = $scriptPath + "\Log\" + $MyInvocation.MyCommand.Name.Replace(".ps1",".txt"); 
(Get-Date).ToString("HH:mm:ss") + " --- Starting script " + $MyInvocation.MyCommand.Name | Out-File $logFile -Encoding OEM; # starting logging to file.
$logSummary = (Get-Date).ToString("HH:mm:ss") + " Script: ExchRate ECB".PadRight(28);
$currExchIDFile = $currExchIDFolder + "GoogleCurrExchIntraday.txt"; if (Test-Path $currExchIDFile) { Remove-Item $currExchIDFile;} # Removing intraday file before each load
$currExchIDFile = $currExchIDFolder + "YahooCurrExchIntraday.txt"; if (Test-Path $currExchIDFile) { Remove-Item $currExchIDFile;} # Removing intraday file before each load
Get-ChildItem -Path $currExchIDFolder -Filter *Intraday.txt -File | foreach { $_.Delete()}; # Delete existing all files from intraday folder

$listStart = $config.IndexOf("<Currency>"); $listEnd = $config.IndexOf("</Currency>"); 
if ($listStart -eq -1 -or $listEnd -eq -1 -or $listStart+1 -ge $listEnd) {"<currency> Symbol list is empty. Exiting script." | Out-File $logFile -Encoding OEM -Append; exit(1);}
$currList = @($config | Select-Object -Index(($listStart+1)..($listEnd-1))); #list of symbols we will work on
$currCount= $currList.Count; "Currency count: $currCount. MinDate: $minDate" | Out-File $logFile -Encoding OEM -Append;
if ($currCount -le 1) {"Found just one currency, will not create currency exchange database"; exit(1);}

$url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip" # online file with historical exchange rates
$file = $scriptPath + "\Log\eurofxref-hist.zip"; if (Test-Path -Path $file) {Remove-Item $file;}
$destination = $scriptPath + "\Log\"; $exchFile = $scriptPath + "\Log\eurofxref-hist.csv"; if (Test-Path -Path $exchFile) {Remove-Item $exchFile;}

$wcD = new-object system.net.WebClient; 
try {$wcD.DownloadFile($url, $file);} # Download exch rates file
catch { $reqFailed++; " Europe Bank Exch Rate History file not found: $url `r`n" | Out-File $logFile -Encoding OEM -Append; "  ECB Exch Rate History file not found"; exit(1); } # if attempt to get File failed
"  Url: " + $url | Out-File $logFile -Encoding OEM -Append;

$shell = new-object -com shell.application; $zip = $shell.NameSpace($file); foreach($item in $zip.items()) {$shell.Namespace($destination).copyhere($item)}; # Unzip files
$exchFC = gc $exchFile; # First line is header and first column must be "Date"
$firstLine = $exchFC[0]; $lsfl = $firstLine.Split(",");
if ($lsfl[0] -ne "Date") {"Error - First line must be 'Date', but we found '" + $lsfl[0]+"'. Terminating script"; exit(1);}

For($currFromIndex=0; $currFromIndex -lt $currCount; $currFromIndex++) {
    For($currToIndex=0; $currToIndex -lt $currCount; $currToIndex++) {
        if ($currList[$currFromIndex] -ne $currList[$currToIndex]) {
            $currFrom = $currList[$currFromIndex]; $currTo = $currList[$currToIndex]; $newData = "";
            $ret = GetCurrExchInfo $currFrom $currTo $currExchFolder $minDate; $currExchFile = $ret[0]; $nextDate = $ret[1]; $lastQuote = $ret[2];
            "CurrExch. From: $currFrom -> To: $currTo. Next date: $nextDate. Quote file: $currExchFile" | Out-File $logFile -Encoding OEM -Append;

            $iFrom = 0; $iTo = 0; $reqRows = 0; $reqCount++;
            for ($i=1; $i -lt $lsfl.Length-1; $i++) {if ($lsfl[$i] -eq $currFrom) {$iFrom = $i;} if ($lsfl[$i] -eq $currTo) {$iTo = $i;}}
            if ($iFrom -eq 0 -and $currFrom -ne "EUR") {$str="Data for currency '$currFrom' not found."; Write-host $str -ForegroundColor red; $str | Out-File $logFile -Encoding OEM -Append; $reqFailed++; continue;}
            if ($iTo   -eq 0 -and $currTo   -ne "EUR") {$str="Data for currency '$currTo' not found.";   Write-host $str -ForegroundColor red; $str | Out-File $logFile -Encoding OEM -Append; $reqFailed++; continue;}
            $reqSucceed++;
            for ($i=$exchFC.Length-1; $i -ge 1; $i--) { #Going 
                $ls = $exchFC[$i].Split(","); $d = $ls[0];
                if ($d -ge $nextDate) {
                    $eFrom = ""; $eTo = ""; if ($d -gt $lastQuote) {$lastQuote=$d;}
                    if($currFrom -eq "EUR") {$eFrom = "1.0"; $eTo = $ls[$iTo];} 
                    else {  if($currTo -eq "EUR") {$eFrom = $ls[$iFrom]; $eTo = "1.0";} else {$eFrom = $ls[$iFrom]; $eTo = $ls[$iTo];} } 
                    if (($eFrom -match "^[\d\.]+$") -and ($eTo -match "^[\d\.]+$")) {
                        $exchRate = ([math]::Round([convert]::ToDouble($eTo,$culture) / [convert]::ToDouble($eFrom,$culture),$roundTo)).ToString($culture);
                        $newData += $d + "," + $exchRate + "," + $currFrom + "," + $currTo + "`r`n"; $reqRows++; $reqRowsT++; 
                    }
                    else {$str="Exchange rate cannot be calculated. Date: $d, CurrencyFrom: '$currFrom', CurrencyTo: '$currTo', Rate1 (From): $eFrom, Rate2 (To): $eTo"; $reqFailed++; Write-host $str -ForegroundColor red; $str | Out-File $logFile -Encoding OEM -Append;}
                }
            }
            if ($reqRows -gt 0) {$newData.Substring(0, $newData.Length-2) | Out-File $currExchFile -Encoding OEM -Append;} # Output to file
            if ($reqRows -eq 0) {" No new data found." | Out-File $logFile -Encoding OEM -Append; }
            else {" New rec count " + $reqRows.ToString().PadLeft(4) + ". Last quote: $lastQuote" | Out-File $logFile -Encoding OEM -Append;};
        }
    }
}

$duration = (NEW-TIMESPAN -Start $startTime -End (Get-Date)).TotalSeconds.ToString("#,##0") + " sec.";
(Get-Date).ToString("HH:mm:ss") + " --- Finished. CurrExch Requested/Succeed/Failed/Rows: $reqCount/$reqSucceed/$reqFailed/$reqRowsT. Duration: $duration`r`n" | Out-File $logFile -Encoding OEM -append;
$logSummary + ". CurrExch Requested/Succeed/Failed/Rows: $reqCount/$reqSucceed/$reqFailed/$reqRowsT. Duration: $duration";
