Can you please change that Qty from 5500 to 550 and refresh just Transactions table. To do so, after change go to PowerPivot, go to Trans table and then from menu choose either something Linked table refresh or just from main menu "Refresh" (NOT refresh all). If that does not work on your full version, can you then try the same on the version you send me.
GRN.TO is priced at 0.75/sh. Can it be something related to a roundoff type of error? (stocks less than $1.00?) What happens if you list buying 500 shares/day over multiple days? (I am assuming you currently split multiple 500 shares over the same day).
Sigh, spoke too soon, I removed the GRN from the account on my original version and i'm still getting the error message. But on the test sheet, it doesn't produce the error when I removed it.
But if I look at the overview tab and look at the calculated fields, you can see something is wrong with how cap gain % and Profit % is calculated.
Look at the YTD Profit % calculation, the individual tickers looks ok but the grant total is off.
A very good point. Here is something you might consider trying:
Create a TOTALLY NEW Account in the "src" tab with a very unique Account name. (ie: Call it "FRED"). Manually add all the transactions that are in the Account giving you "issues" over in the "srcTrans" tab.
If the "Fred" account then works as it should, you can then totally remove the account with "issues", refresh a couple times, (to ensure the original account is cleared from PS). Then, re-name all the "Freds" to the proper final name.
(If you try this, be careful when deleting entries of a table. Delete just the table row, not the Excel row - there might be other tables over to the right of the tab you are working in).
What version of Excel are you using? In Excel 2013 I had to use "trick" to calculate Cap Gain % and Profit %, but since Excel 2016 proper function was added to Power Pivot and I just used normal function. I strongly suspect your issue is with numbers - using current rules Cap Gain % calculation generates exception because my calculated numbers are either to big, or I am getting very tiny negative value. Buddyb suggestion for changing dates here might work, but system should be working without these tricks.
(I wish I had a copy of your file) Ever since I started using PortfolioSlicer, I have been studying up on PowerPivot/PowerBI. There is no magic involved, just structure and numbers (maybe).
Many different internal and external database types can be merged into the single "DataModel" (The Green Tab Collection) of the PowerPivot Engine. From the dataModel you slice and dice your heart's content, to create the various PS Reports..
Perhaps, spending a little time sorting and isolating some of the columns in the Green DataModel might uncover some breaks in various data trends.
Comments
To do so, after change go to PowerPivot, go to Trans table and then from menu choose either something Linked table refresh or just from main menu "Refresh" (NOT refresh all).
If that does not work on your full version, can you then try the same on the version you send me.
What happens if you list buying 500 shares/day over multiple days? (I am assuming you currently split multiple 500 shares over the same day).
But if I look at the overview tab and look at the calculated fields, you can see something is wrong with how cap gain % and Profit % is calculated.
Look at the YTD Profit % calculation, the individual tickers looks ok but the grant total is off.
Here is something you might consider trying:
Create a TOTALLY NEW Account in the "src" tab with a very unique Account name. (ie: Call it "FRED").
Manually add all the transactions that are in the Account giving you "issues" over in the "srcTrans" tab.
If the "Fred" account then works as it should, you can then totally remove the account with "issues", refresh a couple times, (to ensure the original account is cleared from PS). Then, re-name all the "Freds" to the proper final name.
(If you try this, be careful when deleting entries of a table. Delete just the table row, not the Excel row - there might be other tables over to the right of the tab you are working in).
I strongly suspect your issue is with numbers - using current rules Cap Gain % calculation generates exception because my calculated numbers are either to big, or I am getting very tiny negative value. Buddyb suggestion for changing dates here might work, but system should be working without these tricks.
(I wish I had a copy of your file)
Ever since I started using PortfolioSlicer, I have been studying up on PowerPivot/PowerBI. There is no magic involved, just structure and numbers (maybe).
Many different internal and external database types can be merged into the single "DataModel" (The Green Tab Collection) of the PowerPivot Engine. From the dataModel you slice and dice your heart's content, to create the various PS Reports..
Perhaps, spending a little time sorting and isolating some of the columns in the Green DataModel might uncover some breaks in various data trends.
@buddyb if you can pm me your email, i can share my sample file that I shared with Vidas.
You can download this version from this link:
https://support.portfolioslicer.com/index.php?p=/discussion/653/portfolio-slicer-v3-1-0-beta-download-available#latest
This version will have different code (native) to calculate Profit %.
Can you please try and let me know if you still have issues?