Error with Refreshing Data - Date Column

Comments

• VidasMatelis

  February 2022

  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

  February 2022

  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

  February 2022

  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

  February 2022

  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

  February 2022

  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

  February 2022

  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

  February 2022

  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

  February 2022 edited February 2022

  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

  February 2022

  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?