CurrencyConv.csv seems to be missing many dates

ALB

Super excited to start using this product...just getting started, all brand-new fresh downloads as of about an hour ago. I'm attempting to click "refresh all" from the Holdings tab so I can start using the tool!

Unfortunately, I'm getting errors in Excel because CurrencyConv doesn't appear to be completed. It passes validation because the first and last date are there, but there are huge gaps in the middle.

This is what the first few rows of my file looks like. As you can tell, the first date is in there (12/31/2005), and then a massive gap until 1/3/2017. Then it runs all the way through yesterday.

Date ExchRate CurrencyFrom CurrencyTo
12/31/2005 1 N/A N/A
1/3/2017 0.7443 CAD USD
1/4/2017 0.751 CAD USD
1/5/2017 0.7551 CAD USD


12/31/2005 was a Saturday, so I tried changing it to 12/30 just in case, same problem.

The error I'm getting in the Excel file is:
Excel couldn't refresh the table 'CurrencyConv'... [yada]
The operation failed because the source does not contain the requested column. [yada]
The 'CurrencyFrom' column does not exist in the rowset.
An error occurred while processing table 'CurrencyConv'
The current operation was cancelled because another operation in the transaction failed

Appreciate the help here!
Al

VidasMatelis

Delete files from CurrExch folder and then re-run script. Does that help? If no, go to Scripts\Log folder and review logs:
GetExchRatesBoC.txt
GetExchRatesBoC-Old.txt
You can post content of these 2 above files here.

ALB

I've deleted and/or re-run it multiple times, no luck.

GetExchRatesBoC.txt
Starting script. 2017-09-09 09:14:21
Data root folder: C:\Users\Al Bsharah\Dropbox (Personal)\Business\Al Bsharah\Investing\Portfolio Slicer
=======================================================================================
21:14:21 --- Starting script GetExchRatesBoC.ps1. Symbol count: 2. MinDate: 2005-12-30
Found rate file. Looking for quotes starting from: 2017-09-09
Url: http://www.bankofcanada.ca/valet/observations/FXUSDCAD/csv?start_date=2017-09-09&end_date=2017-09-09
Done: USD->CAD (from 2017-09-09 to 2017-09-09). New record count: 0.
Found rate file. Looking for quotes starting from: 2017-09-09
Url: http://www.bankofcanada.ca/valet/observations/FXCADUSD/csv?start_date=2017-09-09&end_date=2017-09-09
Done: CAD->USD (from 2017-09-09 to 2017-09-09). New record count: 0.
21:14:22 --- Finished. Quotes Requested/Succeed/Failed: 2/2/0. New records: 0. Duration: 1 sec.
=======================================================================================



GetExchRatesBoC-Old.txt
Starting script. 2017-09-09 09:14:16
Data root folder: C:\Users\Al Bsharah\Dropbox (Personal)\Business\Al Bsharah\Investing\Portfolio Slicer
=======================================================================================
21:14:16 --- Starting script GetExchRatesBoC-Old.ps1. Symbol count: 2. MinDate: 2005-12-30
Found rate file. Looking for quotes starting from: 2017-09-09
Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_IEXE0101&lP=lookup_currency_converter.php&sTF=to&sT=L_CAD&co=1.00&dF=2017-09-09&dT=2017-09-09
No new data (returned html)
Found rate file. Looking for quotes starting from: 2017-09-09
Url: http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_CAD&lP=lookup_currency_converter.php&sTF=to&sT=_0101&co=1.00&dF=2017-09-09&dT=2017-09-09
No new data (returned html)
21:14:21 --- Finished. Quotes Requested/Succeed/Failed: 2/0/2. New records: 0. Duration: 5 sec.
=======================================================================================


Thanks for the help!
Al

VidasMatelis

Script logs you posted above indicated that currency conversion for 2017-09-08 was already downloaded, so please makes sure that you deleted data all files from CurrExch.
But if above not working you will need to change your currency exchange source from BankOfCanada to European Central bank. For that edit file UpdatePSData.bat.
Change lines from

powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesBoC-Old.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesBoC.ps1
REM powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesECB.ps1

to

REM powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesBoC-Old.ps1
REM powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesBoC.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesECB.ps1

Then before running delete everything from folder CurrExch and re-run.

ALB

Hi again,

Deleted everything in CurrExch, PSData. Same CurrencyConv problems. Same error in Excel.

Modified UpdatePSData.bat to use ECB. Deleted everything in CurrExch, PSData. CurrencyConv looks complete. Same error in Excel.

Deleted everything in CurrExch, PSData, Quotes. CurrencyConv looks complete. Same error in Excel.

Did it all again for good measure, same results.

Appreciate all the help!
Al

ALB

...also:

For good measure, I re-downloaded a fresh copy of V2.3-XL16 and only changed the Min Date in the src tab. Same Excel error when trying to load all data.

VidasMatelis

Could you please send me your Excel workbook (please let me know Excel version) and ALL files from PSData folder. I'll look into this. My email is posted here: http://www.portfolioslicer.com/contact.html

VidasMatelis

Thanks for sending me your data. Please check Error.txt file in your PSData folder - it appears that you have problems with duplicate quotes for symbols "^GSPC" and "^GSPTSE". Please identify why you have quotes and fix that. After you resolve that problem, you should not have any error.txt file after running scripts. Just then try to refresh Excel workbook. Even then during first refresh you still might get error message, but then after that error should disappear.
To resolve duplicate quote - please double check all parameters in psConfig.txt, and understand how you are using symbol rename section. After that try deleteing all files from quotes subfolder and do re-extract of all quotes.

VidasMatelis

I was told by Maxim that there might be a bug in Yahoo provided quotes that might be creating duplicate quotes. He is investigating this and will posted updated scripts when ready.

VidasMatelis

Here is an update from Maxim - over the weekend Yahoo was providing quotes with multiple records with the same date and this caused issues. To recover - delete existing data and re-load data and all should be good. At this point it is assumed that this was one off issue. If this issue will re-appear, then script will need to be updated to handle such data errors from Yahoo. Maxim said this is doable, he just does not want to do this right away if issue will not be repeated.

ALB

Hey again,

Deleted everything (CurrExch, PSData, Quotes) multiple times. I assume that's what you meant by "delete existing data"? Same pop-up re: CurrencyConv error in Excel ("The 'CurrencyFrom' column does not exist in the rowset.")

Since switching to the ECB Exchange Rates script, the CurrencyConv.csv looks like this:

Date ExchRate CurrencyFrom CurrencyTo
12/30/2005 1 N/A N/A
9/11/2017 0.825046 CAD USD
9/8/2017 0.827331 CAD USD
9/7/2017 0.820662 CAD USD
9/6/2017 0.806857 CAD USD
...
1/3/2006 0.859946 CAD USD
1/2/2006 0.860887 CAD USD
12/30/2005 0.859526 CAD USD
9/11/2017 1.212053 USD CAD
9/8/2017 1.208706 USD CAD
9/7/2017 1.218528 USD CAD
---
1/4/2006 1.15319 USD CAD
1/3/2006 1.162863 USD CAD
1/2/2006 1.161593 USD CAD
12/30/2005 1.163431 USD CAD


I don't know if this is normal, but note that the 12/30/2015 date is on top, AND bottom. Top one has N/A's associated w/it. Removing the top row doesn't fix the problem, so doubt that's the issue.

If I delete the CurrencyConv.csv and refresh the data, I get a different error in Excel. So, at least I know it's seeing the right file.

The ONLY error I'm seeing consistently in Error.txt is this one:

Quotes.csv. Date+Symbol should be unique - ERROR. Duplicate records:
2017-08-25,41.990002,WFM

There are definitely two identical rows on that date in Quotes.csv. Removing the duplicate row does not fix the CurrencyConv error in Excel.

Thanks again for working with me on getting this fixed. I feel like we're soooo close!

ALB

...more:

Ok, so...I wiped out everything and started fresh.

No changes, 100% pure configuration, and CurrExchange is still missing everything but 2017 data. So, that appears to be a quirk of some kind. The ECB data seems to be better. Regardless of missing data, the refresh data worked fine in Excel.

After a bit of comparing files and notes, it appears that I modified my delimiter in psConfig from TAB to COMMA at some point in my installation process. I don't know why I did this, I feel like I might have read something in a forum somewhere...but I can't quite recall.

After noticing the difference, I deleted this fresh install and replaced it with the one that we've been working with, and changed the config back to TAB. This appears to have solved the problem.

So...two takeaways for you:
1) ECB currency conversion data is complete, others are not.
2) COMMA delimiter option doesn't seem to work.

And...two takeaway for me:
1) Remember what settings I mucked with.
2) Start digging into the data I'm now seeing!

Thanks again for all the help...