Unable to link PSData to Powerpivot

virosoft

Hi - Please help with the following difficulties, I've been trying to fix for several months!

Refreshing the Excel file gave memory issues, therefore I've done the suggestion to delete several tabs and this does remove the memory error. The data shows up until only March 2018 which is the last time I successfully "Refresh All" the PowerPivot. The PowerPivot linked data is correctly linked to the excel file. My PSDataFolder "C:\PortfolioSlicer\PSData\" has the csv files last updated today as I've run the scripts.

Under Design > Existing Connections.
Each of the CurrencyConv,Dates,Quotes,Dividends are at the correct file path but give me:
"Failed to connect to the server. Reason: Unspecified error"

I feel like the column separator does not have a carriage return option so it won't display the data? How can I link the connection!?

VidasMatelis

What Excel version are you using? Is it possible that PowerPivot on your Excel 2010 is not installed or on your Excel 2013 is not enabled? Please check that first.

virosoft

Office Professional Plus 2010
I'm opening PowerPivot by going to the Excel file and menu "PowerPivot" is available, from this I can click on the "PowerPivot Window" button to bring it up.

VidasMatelis

Can you send me your files from PSData folder? I'll check if format is OK. What is the version for Portfolio Slicer (excel file at the top right should show something like v2.4.08)
My email is posted on this page: http://www.portfolioslicer.com/contact.html

VidasMatelis

Hi,

I can confirm that when attempting to refresh your workbook, memory usage goes to 6GB and then refresh fails. This is most likely to broken link between symbol and quotes as explained below.

I can see multiple issues with your symbol/quotes that you need to address :
- You have 4 symbols that are defined in Symbol table, but there are no quotes for them, example: iSP500
- You have a lots of symbols that you have quotes for, but they are NOT DEFINED IN SYMBOL TABLE. This is most likely why you have issues with memory and known PowerPivot issue. Example of symbols: ^GSPC, ^GSPTSE.

So you must fix these issues above before you can proceed. After fixing issues, attempt to refresh yourself and if you still have an issue, then send me updated files (all) and I'll investigate further.
I'll send you by email list of symbols with issues.

virosoft

Hey as an update - I've done some clean up as suggested, removing old quotes and pretty much making sure there are no retrieval errors in the logs. Still getting the memory issue on refresh (even though it only goes up to 500mb which is 14% of total memory). The PSData PowerPivot refresh is also finally working.

I have another excel file that I removed some tabs, only leaving src, srcSymbol, srcTrans, Daily, Mthly, Yearly, Holdings - This one gives me a different error on refresh "MdxScript(Model) (499,40) calc err in measure 'Report'[Profit $]. Argument function 'LN' wrong data type. Perhaps I deleted too many tabs.

VidasMatelis

Can you send me all the files again?

VidasMatelis

This is what I do when I get files from users:
- Open Excel workbook you send me
- Delete worksheets that I do not need. I keep just following worksheets: src, srcSymbol, srcTrans and Mthly-Symbol. After deleting every worksheet I save Excel workbook as sometimes during deletion Excel crashes. Sometimes I have to delete all reports in the worksheet and leave empty worksheet, otherwise, Excel crashes.
- In Mthly-Symbol worksheet remove filters from slicers Account and Year and make ReportCurrency slicer value set to "*Original"
- In Mthly-Symbol worksheet select one by one symbol and record symbols that return an error.
- In Mthly-Symbol worksheet remove "Cap Gain %" and "Profit %" measures
- In Mthly-Symbol worksheet again select symbols that previously had errors. This time there should be no errors, but you will see gaps in months - some months will have no records and that means these months did not have quotes. For buy/sell months you might have record even there are no quotes. But you should then look into why there gaps.

I will send you by email list of symbols that still have issues - no quotes in some or all months.

I still see an issue that you Symbol slicer has Empty symbol at the end - this indicates that you have symbols that are not defined in Symbol table. Please check dividends file and confirm that all symbols are defined.

virosoft

Hey Vidas,

Thank you for sharing how to debug!
I added my own AlphaVantageKey instead of Demo, this retrieved 2 stocks historical info (QCOR,TNH). Every other website does not have data.

Since I could not download quotes for the following symbols, I've created/simulated all the daily values based on start buy and end sell transaction prices.
OK -> EGAS - many quotes missing, example 2015-01..2015-03
OK -> SXL - many months missing, example: 2013-04
OK -> TWC - quotes missing, example 2012-05
OK -> TU - quotes missing, example: 2012-04, 2012-05, etc
OK -> CBY.TO - missing quotes
OK -> CJP.TO - missing 2017-03, 2017-05 quote
OK -> T.TO - missing quotes, example: 2012-05
OK -> TOP.CN - no quotes for 2014-05, 2014-06

The debugging workbook with Mthly-Symbol tab now works for full symbols! Unfortunately, the full PortfolioSlicerV2.4.08-XL10.xlsx workbook still halts on memory errors during the refresh. !!!

VidasMatelis

Hi,

You did not give me your PSData folder files, so I cannot test this myself, but could you:
1. Delete quotes from your quotes folder for symbols: ^GSPC and ^GSPTSE. You do not have these symbols defined, but you have quotes for them.
2. Run UpdatePSData.bat file to create new psdata folder files.
3. In excel 2010 update PowerPivot data model with new data from these psdata folder files.
4. Save file.
5. Attempt to refresh Excel reports. If you still have issues, please send me your Excel and PSData folder files.

virosoft

Hey - I've removed these quote files and refreshed as you instructed. The full excel file still does not refresh, while the debug file does, however, refresh fine. Thanks.

Memory error : Allocation failure : Not enough storage is available to process this command.

VidasMatelis

I refreshed your workbook on my PC and it worked without any problems - but it was slow - took about 3min. I believe this is due to the fact that you have so many symbols AND BECAUSE you are using generated dividends functionality and that is known to slow down PS.
During refresh, I saw that PS was using about 1.5GB of RAM and that indicates to me that you must have Excel 64bit. If you have Excel 32bit, you probably will not be able to refresh this workbook.
You can try additional steps to reduce memory:
1. Delete worksheets with reports that you are not using.
2. For some worksheets select slicer to limit data that is used for calculation - for example in worksheets you can select very first account "B..." that has no transactions, so this will limit data used.

You can also try to delete all dividends.csv records (leave just header and first dividend for *Cash) and see if that helps.