Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Error messages - Refresh All fails
I am getting the following error messages when updating the Reports spreadsheet:
- First message
- Second message
Gary
- First message
- Second message
Gary
Comments
First thing - please check tables Symbol and CompareTo - and make sure you accidentally did not rename column names.
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.
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?
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
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
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
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.
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.
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