Error messages - Refresh All fails

gsbaker

I am getting the following error messages when updating the Reports spreadsheet:
- First message

- Second message

Gary

VidasMatelis

What version of PS are you using - 2.4 or 3?
First thing - please check tables Symbol and CompareTo - and make sure you accidentally did not rename column names.

gsbaker

Using ver 3.0.0. Compared the column names in my Source file to the Source file in the download and they are the same.

I have also tried:
- rebuilding the Source file starting with the download original and copying over the data from my Source file.
- doing a refresh All using a previous copy of my Reports spreadsheet that I know worked;
- deleting the .csv files in the C:\PortfolioSlicer\PSData folder and re-running the scripts
- cold re-boot of my laptop and trying again

Still got the same error messages.

Also having an issue with the download of BoC exchange rates, but will leave a post about that issue on the "Exchange BOC error" discussion thread.

VidasMatelis

First you need to address all errors you have. That is after your script runs, there should not be error.txt file in c:\PortfolioSlicer\PSData folder. Just after that you can try refreshing data in Excel.
Can you please also clarify how you encountered this error. Specifically, were you able to do refresh last week, but this week it stopped working?

gsbaker

When running the full UpdatePSData.bat script (unnecessary lines REM'd out (see below).



the only "error" message that appears on screen is in the "CurrencyConv check" section of the psCheckFiles.ps1 script, which reports the same BoC error noted in the other thread. After manually correcting the Exchange Rate text file and re-running the above script, no errors are reported (since the exchange rate text files have been corrected no additional rates are retreived from BoC)

I first encountered the error this past weekend - they ran Ok the previous weekend. I run the scripts on Saturday mornings to ensure I get the end of week quotes and all exchange rates for the week.

Between those last 2 runs I was working in both the "Reports" spreadsheet and the "Source" spreadsheet. I was working on the "TransInfo" data that I referred to in another post. I don't know DAX, so my thought was to create a tab in the Source spreadsheet to capture the data I needed, so I built a PivotTable and Slicers with the Transactions table (in the Transactions tab) as the source data for the pivotTable, to be able to create a "report" I was looking for.

I also created a second new tab in the Source file to create a pivotTable that would give me securities "purchased" in the year. When I had that looking like I wanted I "moved" that tab to the "Reports" spreadsheet and moved the pivotTable on to the "Sales" tab (so that Sales and Purchases, while separate pivot tables, could appear on the same report). This isn't quite looking the way I want it yet, but that is a different issue (I think :-).

When I got the error messages on the "Refresh All" process, I have tried reverting to a version of the Reports spreadsheet that was previously working, as well as re-building the Source spreadsheet from the original download zip file. Regardless of revertiing back to previous versions, I still get the error reports on the Refresh All process as noted above.

I have made extensive changes to the Reports spreadsheet - mostly to adjust the layout of the reports. But everything was working Ok until this past week.

Gary

VidasMatelis

1. Because you applied some customization on Source workbook - please make sure that script GetExcelSrcData.ps1 does not generate any errors. Please check Scripts\Log\GetExcelSrcData.txt andGtExcelCostBasisi.txt files
2. Check file c:\PortfolioSlicer\PSData\Transactions.csv - you can open it in any editor. In that file you should see tab separated data, for example:


Please check other files in that folder - they should have a similar format. Especially look into the files that you get message about during the refresh.

If that does not help, then if possible, adjust files (for your privacy) and send me (Excel source, Excel reports and all data from folder c:\PortfolioSlicer\PSData). Before sending me anything make sure that on your adjusted report you can reporoduce this issue. My email is posted here:
http://www.portfolioslicer.com/contact.html

gsbaker

My Scripts\Log\GetExcelSrcData.txt and GetExcelCostBasisi.txt files do not show any errors:





The c:\PortfolioSlicer\PSData\Transactions.csv appears to be formatted correctly with tab separated data as do the Symbol.csv and CompareTo.csv files.

I will work at getting you the files soon.

Thanks so much for your help on this!

Gary

VidasMatelis

Gary, I got your files.

In c:\PortfolioSlicer\PSData folder script creates file schema.ini that describes what columns are in each file. If you will open schema.ini in any text editor, you will see that this script file is not matching your table structure - for example Symbol file is missing "Currency Base" column and Config file has many extra columns that do not exists anymore.

The file schema.ini is created by the script psMakeAllDataFiles.ps1. It appears that you are using older version of this script. Please use latest version of this script (for version v3.x) and you should be OK. As you have so many customizations, you are likely have custom code in that script. In that case simply copy overy "## Creating file schema.ini if column separator is Tab" section to your existing script.

As soon as I updates schema.ini file I was able to refresh your data.

HTH.

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.

gsbaker

Thanks Vidas. I do have lots of customizations, however I have not changed any of the scripts. I think what happened was when applying the fix for the "Out of Range Exception Error - YahooQuotes Script" reported by joelm, I updated all of the scripts with the v2.4.17 extract scripts, and not just the "GetQuotes-Yahoo.ps1" script. This copied over the scripts for the older v2.4 Excel files as you note above.

I have replaced all of the scripts with the v2.4.18 extract scripts, and "harmonized" my excel Source file with the v3.0.0 Demo Source file.

It now completes successfully except for the BoC error that I reported in the discussion titled "Exchange BOC error", so that thread remains open.

Thanks again
Gary