Error with Refreshing Data - Date Column

virosoft

Hello!

I'm having an issue refreshing my data in PortfolioSlicer.
It is giving the error in the provided screenshot.
... does not contain the requested column..
.. The 'Date' column does not exist in the rowset ... error occured while processing table 'Dates' ...

If I go to PowerPivot - Manage to see the Data. There is indeed the column Date inside of the Dates table.
This occurs in both:
PortfolioSlicer3.0-Reports-Light
PortfolioSlicer3.0-Reports

The data is being populated correctly inside of:
C:\PortfolioSlicer\PSData\Dates.csv

With the use of the UpdatePSData - 32Bit.bat tool.

Any insights to this error message?
I don't recall doing anything abnormal to cause the issue!

VidasMatelis

In Excel workbook when you go to PowerPivot menu you see Date column because that data is old - when refresh fails, data is not updated.
Go to PowerPivot table list again (First screenshot in your post) and then select "Existing Connections". Select Dates connection and click edit.
- Is File Path OK? Should be c:\PortfolioSlicer\PSData\Dates.csv
- Did Excel recognized Dates table with just one column Date, or did you get an error message?

virosoft

Hello Vidas - Thanks for the follow-up.

Here is the screenshot of what I see in the existing connection of Dates.


I'm not sure what the warning on the bottom means, but it is showing for each connection.
"A Schema.ini file has been detected in the current import folder "c:\PortfolioSlicer\PSData". Settings from this file will override your current import settings. "

VidasMatelis

Warning is expected. Next steps:
1. Check if c:\PortfolioSlicer\PSData folder has error.txt file. If so, it will provide more information about the error.
2. Check transactions.csv file - does it have Date column?
3. Check PortfolioSlicer-Source.xlsc file, Transactions worksheet - does it have date column?
4. Try manually reloading each table - start with Dates, then Transactions, etc. To do so open Reports-Light excel file, then PowerPivot, Manage, select table Dates, then choose "Refresh" and then "Refresh" again (NOT "Refresh All"). Are you failing on Dates table or other tables?

virosoft

1. No error.txt found within:
C:\PortfolioSlicer\PSData\

2. Transactions.csv file within PSData seems to have working dates.
Examples:
"MH-QT-LIRRSP-CAD 2021-08-18 Buy CUD.TO 22 51.85 0.08 1 1536 1140.78"
"MH-QT-LIRRSP-CAD 2021-08-31 DivTA CUD.TO 1 1.72 0 1 1625 0.0"
So does TransactionsInExcel.csv in a different format.

3. PortfolioSlicer-Source.xlsx file has a date column. I attached a screenshot of the last lines of data.

4. PowerPivot > Manage > Refresh
Dates gives the error in the screenshot below.
CurrencyConv gave the same error as Dates

Symbol refreshed/transfered rows.
So did: Account, Allocation, Config, ReportCurrency, SymbolSector, CompareTo, TransType, Transactions, Report, Quotes, SymbolAllocation

Sample of the data inside of CurrencyConv.csv file within PSData
"Date ExchRate CurrencyFrom CurrencyTo"
"2012-01-23 1.0000000001 N/A N/A"
"2019-12-31 0.685025 CAD EUR"
"2020-01-02 0.687332 CAD EUR"
"2020-01-03 0.691037 CAD EUR"

VidasMatelis

It appears that your data is OK, but the issue is with Excel.
I have seen a similar problem once, but that was on the computer where 2 different versions of Excel were installed side by side.
Could you please try to repair the Excel version you are using:
Windows Start->Settings->Apps->Microsoft Office->Modify->And try fist Quick repair.

Let me know if that changes anything.

To confirm that Excel is the problem you can do the following:
- Start the new Excel workbook, go to PowerPivot->Manage, choose "From Other Sources", select Dates.csv file from c:\PortfolioSlicer\PSData folder and try to load. Is the load succesful?

virosoft

Dates on their own do load just from the csv. See the images below.
It's the report with the extra columns that isn't updating correctly.



The full columns:

VidasMatelis

Can you send me all your files from PSData folder? My email is posted on this page: http://www.portfolioslicer.com/contact.html

For privacy reasons you can delete some transactions, but make sure you try to refresh after deleting transactions and get the same error.

VidasMatelis

Thanks, I received your files. It is interesting problem that I do not understand.

If in Excel I go to PowerPivot window, then "Existing connections", then select "Dates" and hit "Edit" I see column name not "Date", but "special symbols + Date". But text editor that file looks OK, so I do not understand:



File Dates.csv is created by psCreateDatesFile.ps1 PowerShell script. Any chance you change that script? What is the dateStamp on that script on your computer?

virosoft

psCreateDatesFile.ps1 2021-01-28 Date Modified - Last time I reinstalled windows.
I've been troubleshooting and trying things to fix this issue without success.
How can I "Reinstall" my portfolioslicer perhaps?