ps is failing to refresh due to external file (availability)

spep99

Hello,

I'm following the plan and slowly adding more transactions (and symbols) to build confidence in the system of tools and scripts. I had everything working yesterday morning, but when I added a few more Mutual Fund transactions (common symbols; all available on Yahoo)...all of a sudden the Refresh failed. I get the following message:

Excel couldn't refresh the table 'CurrencyConv' from connection 'CurrencyConv'. Here's the error message from the external data source:

The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.


Now, this is strange to me because the CurrencyConv.csv file is in the same place it always was. I checked the details, and the most recent dates/data have been appended.

I notice that there are errors in the GetQuotes-Yahoo log; specifically only 3 of the 11 symbols could be looked up. I believe this could be happening because I'm sending the query from EU time/date and the Yahoo site is not yet complete for today (in North America time)??? I don't know.

But that would not explain the CurrencyConv problem. I note also, that the date of the record in PS is not updated either, which i guess is related to the failure to refresh this table.

Anyone have ideas about what I could look for?


Cheers

spep99

VidasMatelis

When you get such an error, the first thing you should do - close Excel, then start it and try to refresh again. I started to get a similar error a few months ago after applying MS Office updates (Office 2016).

spep99

Thank you, Vidas! This did indeed allow the refresh to work properly. Shutting the file alone and reopening did not work...I had to shut down excel altogether and restart.

qosmioamit

I am getting this error while refreshing PS3.0-Reports-Light (and full version). I already tried Excel shutdown and even a computer restart. I can run UpdatePSDatafromExcel without any error prompt. I am running Excel 365. I even ran the 64-bit version of the Access 2010 engine. I added PSData to the Trust Centre too.

VidasMatelis

Please check file Sybmol.csv in the folder c:\PortfolioSlicer\PSData
Last column in that file should be CurrencyBase. Do you see that column?

qosmioamit

I read your comment. I deleted all the files in PSData and re-ran the script.
I do see it except it's altogether (the same as it was before)



Excel refresh still does not work. PowerBI works though.

VidasMatelis

Please check one more thing as I just had this issue with another user.
Please open c:\PortfolioSlicer\PSData\schema.ini file in any text editor and check section [Symbol.csv]. Do you have all 10 columns listed as below?

[Symbol.csv]
Format=TabDelimited
TextDelimiter=none
DateTimeFormat=yyyy-mm-dd
col1=Symbol Text
col2=SymbolName Text
col3=Currency Text
col4=MER Double
col5=Allocation Text
col6=SymbolGroup1 Text
col7=SymbolGroup2 Text
col8=SymbolGroup3 Text
col9=Region Text
col10=CurrencyBase Text

qosmioamit

Yes, col10 was missing. I added it and a different error shows up:

qosmioamit

I picked up what you were putting down. The schema.ini file was missing information and it wasn't getting rebuilt properly. Luckily, I had a backup. It works now.

I have attached it as a .zip if anybody else has the issue. Simply unzip it and place it in the PSData folder overwriting the existing one.

Thanks!

VidasMatelis

Schema.ini is recreated every time your run script psMakeAllDataFiles.ps1.
Make sure you are using latest version of this script.

VidasMatelis

I believe I screw up with releasing v2.4.17 extract scripts - they were for the older version v2.4 Excel files.
I just release v2.4.18 extract scripts that should work well with v3.1 Excel files.
http://www.portfolioslicer.com/download/v2.4/PortfolioSlicerDDMScriptsV2.4.18.zip

I know that script version naming makes it confusing too.
I will work on doing proper v3.1 release - for excel files and extract scripts in the near future.
Sorry about that.

qosmioamit

No worries. I used the script from the download section instead of the version in the forums. Then to make it work, I used some files from an older version and ended up in a complicated mess of files from different versions. It's all sorted with v2.4.18 from your post.