Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Problems getting started.
I have populated the all the tables with my required information (ensuring there are no blank rows). As a test I tried to refresh the Holdings Pivot Table to see if it lined up with my actual holdings.
On a refresh I get the following Error:
"The query did not run or the Data Model could not be accessed. Here's the error message we got:
MdxScript(Model) (20, 4) Calculation error in measure 'Report'[Dividend%]: An argument of function 'LN' has the wrong data type or the result is too large or too small."
This Error occurs twice, and the table updates some of its Values, but it does not show Cost Basis, Total Value, Mgmt Fee, Cost Basis Per unit, Symbol Price, etc. I have manually entered DivTA transactions, and they all appear correct. I have also checked that the UpdatePSData.bat has completed successfully, and generated some .csv files in the dividend folder.
None of the other reports appear to populate properly either and some cause the same error to appear. I am at a bit of loss at this point, and any help would be greatly appreciated. Thanks in advance.
On a refresh I get the following Error:
"The query did not run or the Data Model could not be accessed. Here's the error message we got:
MdxScript(Model) (20, 4) Calculation error in measure 'Report'[Dividend%]: An argument of function 'LN' has the wrong data type or the result is too large or too small."
This Error occurs twice, and the table updates some of its Values, but it does not show Cost Basis, Total Value, Mgmt Fee, Cost Basis Per unit, Symbol Price, etc. I have manually entered DivTA transactions, and they all appear correct. I have also checked that the UpdatePSData.bat has completed successfully, and generated some .csv files in the dividend folder.
None of the other reports appear to populate properly either and some cause the same error to appear. I am at a bit of loss at this point, and any help would be greatly appreciated. Thanks in advance.
Comments
Please follow these steps:
1. Make a copy of your Excel workbook and work on the copy version. Keep original safe so you can come back at any time when you will figure out what the problem is.
2. Delete most of the transactions from Trans table - leave just first system transaction and one more of your deposit transactions. Make sure there are no buy/sell/dividend transactions - refresh your workbook.
3. If you have issues in step 2, review your existing Account/Symbol/Report currency tables - makes sure they have no empty rows and no red cells.
4. If step 2 refreshed without problems - delete existing workbook and copy it again from original version. Then leave few more transactions than before and attempt to refresh. Do that till you add transactions that cause problems.
5. When you hit issues in the step 4 - last few transactions you added are causing issues.
From my experience - error you get usually indicates issues with data. You might have transactions that do not make sense for PS, so calculations fails. Specifically - you have no holdings for specific symbol when you receive dividends (adjust transaction date for dividends or sell date), or most often you have negative cash value after purchase - please review transactions carefully.
If after these steps you cannot find issue, you can send me your workbook and content of your PSData folder and I'll review. My email is posted on page: http://www.portfolioslicer.com/contact.html
Regards,
Following your instructions I was able to eliminate the error that was occurring by offsetting my dividend dates by -2days (there were several that were reported after a sale of a symbol), but it appears most of the data in the reports and PivotTables is still not updating.
I started with a clean spreadsheet extracted from a zip file downloaded from your website and added in my information one piece at at time. First Accounts, Then Symbols, Then Allocations, and finally Transactions in blocks of 3 months.
I have e-mailed you a copy of my Spreadsheet and PSData folder.
Once again thanks for your assistance.
Thank you for sending your files.
This is what I did:
1. I reviewed your Excel file - there were no red cell and no empty rows in Excel tables.
2. I checked tables in PowerPivot window - you can do that just if your Excel version has full support for Power Pivot. I noticed that all PowerPivot tables that derive from Excel tables were populated with YOUR data, but all external data file tables had just data that comes with empty Portfolio SLicer Excel workbook. That is clear indication that issue is with external data files.
3. I reviewed your external data files and they looked OK - they are all TAB separated and from quick view had necessary data. Date table had dates from your MinDate up to latest business day.
4. You confirmed that your PSData folder is in C:\PortfolioSlicer folder. As Excel workbook by default is looking for external files in this folder c:\PortfolioSlicer\PSData - that confirmed that Excel workbook had access to right external data files.
5. You confirmed that your PSData folder does not have file schema.ini - and this is the reason your refresh fails. Your PSData folder MUST contain schema.ini file that tells Excel in what format to expect external data files. This file is created every time you run .bat file. Specifically there is script MakePSDataFiles.ps1 that has lines at the end to create this schema.ini file. Somehow it appears you do not have correct version of this file (MakePSDataFiles.ps1). Correct version should have date of 2016-04-21.
To fix your issue - please re-download "External data Download and Management script set (for majority of users)", UNBLOCK zip file, extract .ps1 files and copy over files from extract to your existing folder that currently has these .ps1 files, then rerun .bat file and your Excel refresh should work. From the screenshot you send me I can tell you that you will need to update your .bat files as they are from the older version too!
After downloading the script files again and copying over the files I had, everything is populating properly.