Known Portfolio Slicer Limitations

Here is the list of known Portfolio Slicer limitations

  • Supports maximum up to 3 different currencies.
  • When calculating Cost Basis, Portfolio Slicer will return value of 10mln when specific symbol was sold partially (!) more than 5 times since last complete sale. More about tracking cost basis is described here.
  • Depending on the Excel 2013 version (Office 365/Home/Student/Professional/etc), you might not be able to change external file location.

 

Cost Basis limitation workaround

If you are OK with having “Cost Basis” calculation done just for one currency (account where transactions was entered), then there is a workaround for current “Cost Basis” calculation limitation - you can move calculation into Excel and use formula to populate value in “CostBasisOverride” column. To do so follow steps:

  1. In srcTrans worksheet remove all filters from table (if you have any).
  2. In cell “X1” enter “QHBS” (without quotes). This will create new column QHBS (means Qty Held Before Sales).
  3. Select cell “X2” and then in formula bar enter formula:
    =IF([@TransSubType]="System",0.00001 , IF([@Symbol]="* Cash", 0 , IF([@QtyChange]>=0 , SUMIFS(T$2:T$80000,A$2:A$80000,"="&A2,B$2:B$80000,"<="&B2,V$2:V$80000,"="&V2,W$2:W$80000,"<="&W2) , SUMIFS(T$2:T$80000,A$2:A$80000,"="&A2,B$2:B$80000,"<"&B2,V$2:V$80000,"="&V2,W$2:W$80000,"<>"&W2) + SUMIFS(T$2:T$80000,A$2:A$80000,"="&A2,B$2:B$80000,"="&B2,V$2:V$80000,"="&V2,W$2:W$80000,"<>"&W2, T$2:T$80000,">0") ) ) )
  4. In cell “Y1” enter “CBI” (without quotes). This will create new column CBI (means Cost Basis Impact).
  5. Select cell “Y2” and then in formula bar enter formula:
    =IF([@TransSubType]="System",0.00001 ,IF(INDEX(TransType, [@TTR], 5)=0 , IF(INDEX(TransType, [@TTR], 7)=1, -1*[@TotalAmnt], IF(INDEX(TransType, [@TTR], 8)=1, [@TotalAmnt], 0) ) , IF(LOWER([@TransType])="split", 0 , IF([@QtyChange]>0, [@TotalAmnt] , IF([@QHBS]=0, 0 , ROUND([@QtyChange] * (SUMIFS(Y$2:Y$80000,A$2:A$80000,"="&A2,B$2:B$80000,"<"&B2,V$2:V$80000,"="&V2) + SUMIFS(Y$2:Y$80000,A$2:A$80000,"="&A2,B$2:B$80000,"="&B2,V$2:V$80000,"="&V2, W$2:W$80000,"<>"&W2, T$2:T$80000,">0") ) / ([@QHBS]) , 2) ) ) ) ) )
  6. Select cell “K2” (column CostBasisOverride). In formula bar enter formula:
    =IF([@TransSubType]="System",0.00001,IF([@CBI]<0,[@CBI]*(-1),[@CBI]))
    This formula has to be applied to all rows - in some cases this will be done as soon as you entered formula in cell “K2”, in other cases you will have to copy this formula (drag corner of the cell down) to all rows in that column.
  7. Refresh your Portfolio Slicer data.