Canadian Money Forum banner

Portfolioslicer (Automated Excel portfolio tracker)

Tags
excel track
85K views 159 replies 17 participants last post by  larry81 
#1 ·
http://portfolioslicer.com

Discovered this workbook today... anyone use it ? It look very nice !

I currently use Quicken but portfolioslicer seem much more complete.
 
#4 ·
When you want to do your investment tracking - first thing you need is a list of your transactions. And full list of transactions includes deposits, dividend payments, etc. To enter all these transactions is not an easy task. With Portfolio Slicer you will start building your transaction history in Excel and if later better products will be released or something happens to Portfolio Slicer, you will be able easily transform those records into different format.
Portfolio Slicer supports many more stocks than google finance and it can produce much more advanced reports that you could with google spreadsheet.

Disclosure - I am owner of Portfolio Slicer.
 
#5 ·
i would love to try, i use google sheets and finance and can get some excellent basic information (it leaves out dividend yields as an example of where it fails, though you can do a workaround)

unfortunately, i use a mac, do you plan on releasing a mac compatible version ?

i find excel 2011 just to much work to bring in stock quotes though i have been able to do it
 
#7 ·
vidm, i didnt know you posted on this forum :)

Could you explain how a "Norbert Gambit" transaction should be handled with Portfolio Slicer ? I have a hard time figuring how to enter the pair of transactions without breaking everything !
 
#10 ·
Hi Kaitlyn,

Portfolio Slicer has special transaction types that are designated to deal with "cashless" portfolios:

Buy+Deposit
Div+Withdraw
DivTA+Withdraw
Sell+Withdraw

These transactions assume that you deposit or withdraw cash amount that is equal to total transaction impact to cash. Using these transaction types will make Portfolio Slicer work without tracking cash.
Just a note - I have not done as extensive testing of these transaction types, but if you will encounter any problem, I'll do my best to resolve it ASAP.

Regards,
 
#14 ·
Webber22,

All formulas in Portfolio Slicer are open to every user - that means every one can edit them and adjust them the way user wants. I also can just post formula on my website or in the forum and you can apply fix yourself. Portfolio Slicer is very open system - if you know Excel, you can design any report the way you want it. Because of that flexibility - same users choose Portfolio Slicer.
 
#19 · (Edited)
I am entering all my transactions as well, so far its very nice :)

Here is two question regarding data entry:

Question #1
For maximum consistency sake, how should a fee refund be entered ?

Method 1 - Deposit
Method 2 - Positive BankFee

Question #2 -
Is there a way to enter a dividend transaction without having to specify how many shares I held at the time the cash was distributed to shareholders ? From what i understand, DivTA could be used with a shares value of 1 ?

:)
 
#20 ·
I am entering all my transactions as well, so far its very nice :)
Question #1
For maximum consistency sake, how should a fee refund be entered ?

Method 1 - Deposit
Method 2 - Positive BankFee

Question #2 -
Is there a way to enter a dividend transaction without having to specify how many shares I held at the time the cash was distributed to shareholders ? From what i understand, DivTA could be used with a shares value of 1 ?

:)
Q1: Normally when you enter transactions in normal circumstances you enter positive values. So for buy and for sell when you enter qty or price - you enter them as positive values. If bank charges you lets say 50$ for account, you would enter positive 50$ for charge and for refund you would enter transaction with type BankFee but with negative TransPrice as -50.

Q2: You are right about DivTA - for such transaction TransQty field is ignored and you can enter 1 and then in TransPrice you would specify total dividend received amount.

Regards,
 
#21 ·
Thanks for the reply vidm, its great the have you here :)

Regarding Q1, the way you suggest handling fee (a transaction with a positive number) goes against what is usually displayedon banks/brokers websites and quicken.

Regarding Q2, i am not sure how to enter my DRIP... let me give you an example from my very own TDDI statement:

13-Aug-2014 ZRE DRIP 7 $139.89
13-Aug-2014 ZRE TXPDDV -1,905 $158.12

i received a distribution of 158.12$, 7 ZRE unit DRIP'd (for a total of 139.89) the remaining went into the cash position.

How would i enter this in portfolioslicer ?

Thanks for helping :)
 
#22 ·
Regarding Q1 - I had my doubts about my approach, but I believed that having all standard transaction sign handled by transaction type implicitly instead of user trying to figure out if this transaction should be + or - should be less confusing. Most of my transactions are with TD and they report 2 columns: "Credited" & "Debited" but again some people would have problem which sign to use with each transaction. But I will consider adding transaction types that would expect positive/negative amount...

Regarding Q2 - with your example you would have 2 records:
Record 1:
TransType: DivTA
TransSubType:
TransQty: 1905 ( or you can specify here anyting....)
TransPrice: 158.12

Record2:
TransType: BuyTA
TransSubType: DRIP ( I specify here usually DRIP for all reinvested dividends, but this string can be anything - it is just a grouping of transactions. It just helps me later quickly filter just DRIP type transactions.
TransQty: 7
TransPrice: 139.89

Actually that is exactly how my bank (TD) reports this to me and that is how I enter these transactions into PS.
 
#23 · (Edited)
Thanks for the quick reply vidm, make sense !

There might be some room for improvement in the naming of various fields

Src tab
“Portfolio” should be renamed “Account”, I suspect that in the majority of the case, it is really account that we are talking about here (ex: TFSA, RRSP, Holding company, etc.). A portfolio is composed of a variety of account which fit your model (ex: a family portfolio composed of two 2 RRSP and 2 TFSA)

  • Portfolio = Account (ex: Larry-RRSP)
  • Portfolio Group = Portfolio (ex: Family)
  • Portfolio Tax = Taxable (with True/False value)

SrcTrans tab
Get rid of the « Trans » prefix, there no need for redundancy here

  • Portfolio = Account
  • TransDate = Date
  • TransType = Type
  • TransSubType = SubType
  • TransSymbolName = SymbolName
  • TransQty = Qty
  • TransPrice = Price
  • TransFee = Fee
  • ExchRate = Rate
  • Comment = Comment
  • BookValueOverride = BookValueOverride
  • AccruedInterest = AccruedInterest
  • TransTotalAmnt = TotalAmount
  • Symbol = Symbol
  • TransID = ID (TransID could also be used here)

:)
 
#24 ·
Larry81,

Thank you very much for your suggestions. I consider renaming Portfolio to Account for some time now. You are absolutely right - for majority of people Portfolio = Account. I will consider renaming it for the future release. I also agree with you on other fields - shorter names makes more sense now. Previous releases used to have for example TransQty and then there was internally hidden calculated field Qty. That is why I have Trans prefix. But then I removed calculated fields and now removing Trans makes sense.

Again than you very much for your suggestions and if you have any more - please please let me know!
 
#25 ·
Here another quick suggestion, in the "SrcTrans" sheet, it would be practical to have an "AccountCash" column tracking the current cash value of an account. When adding historical transactions, it can be hard to determine if everything balance correctly. Or maybe this information is available somewhere else ?

So far i really like portfolioslicer, should also help me lower my accounting costs :)
 
#26 ·
In PS I calculate Account current Cash on the fly - based on your transactions. When I enter historical transactions, I first enter all transactions, then I refresh PS and use BankStmt reports to make sure that number match. In BankStmt worksheet you have selected period (I usually check by month) Start and End Cash values. So if you select one month you can see what was cash value in your account for start of the month and then at the end of the month.
I hope you can use that information to balance your data.
 
#28 ·
FOr split transaction you enter:

TransType: Split
TransSmbolName: MIP123
TransQty: 90629.361
Other fields can be empty.
I assumed that numbers in that line means existing quantity and new quantity, so I calculated that: 90629.361 = 100699.29-10069.929

TransQty field here specifies by how much change quantity of that stock - positive value means to increase quantity and negative value is to decrease quantity. So you can easily do split up and split down.

Don't worry about "Bugging me" - all your questions show gaps in my documentation. I'll use your questions to update my documentation and I'll use your name change suggestions too.
 
#29 · (Edited)
I had: 100,699.29 share valued at 1$ each

After the split i have: 10,069.929 valued at 10$ each

I am not sure i am entering this properly, PS show a lost of 90k for this position

SymbolName First Trade Date Qty Held Book Price Price Price Orig Curr Book Value Adj Cost Base Total Value Unrlzd Cap Gain
MIP123 2010-11-12 10,078 10.00 1.00 1.00 100,779 100,779 10,078 -90,701

--
Good news regarding the name change :)

Another tidbit, where it make sense, PF should use "Accounting" or "Currency" field type when possible.

Two obvious place
Start Cash and End Cash in the "Bank Stmt" tab
 
#30 ·
Larry,

SO you have 10: 1 split (looking from quantity perspective - for each 10 shares you got 1 share). You ignore price change as that is handled by quote. So you should enter Qty: -90629.361. Your quantity change by -90629.361 - you now have less shares.

Could you please clarify what you mean by :
PF should use "Accounting" or "Currency" field type when possible.

What is PF here?
 
#32 ·
For most reporting measures I use rounded values as this way it is easier to read data. I found that with decimal displayed report gets too busy.
But for Bank Stmt report I agree - you need cents. I'll fix report for next release, you can fix now by selecting cell with value, then "Value Field Settings...", then click on "Number Format" button and then choose Excel format that you want. Such change would apply just to that report.
 
#33 ·
vidm you really need to add a column "TotalAccountCash" in the srcTrans tab. This will make account conciliation much more easier, since statement are often from 15 to 15 (instead of end of the month) the Bankstatement is not very useful.
 
#35 · (Edited)
Welcome back vidm, thanks for taking the time to look into this !

Just to make this clear, the user should not have to manually enter value in this column, this should be computed internally. Here is a quick, simplified example:

Text Font Line Games


Would help a lot for making sure the transaction are entered properly. Monthly report come one a month and are usually from 15 to 15. This field should compute the current cash balance of the account where the transaction is taking place.
 
#38 ·
I am still not sure when I'll do next release. I do not want to promise anything yet.

Now good news is that I was able to add cash balance to transaction table. If you do not want to wait for next release - you can easily add calculations yourself.
Steps:
1. In srcTrans worksheet in the next column (P) enter formula into cell P2: =[TransTotalAmnt] * VLOOKUP([@TransType],tblTransType[#All], 5, FALSE)
Call that new column TransCashImpact
2. In srcTrans worksheet in the next column (Q) enter formula into cell Q2: =SUMIFS($P$2:$P$99999,$A$2:$A$99999,"="&A2,$B$2:$B$99999,"<="&B2,$O$2:$O$99999,"<="&O2)
Call that new column CashBalance.

That is the power of Excel and power of having your transactions in Excel workbook!

Thank you again for such great suggestion! I used CashBalance myself to see if my transactions are up to date and found it very helpful!
 
#41 · (Edited)
vidm i just finished entering 5years worth of transactions in Portfolio slicer, i really like all the stats and data !

Here a couple questions to make sure i am using it properly:
1. In the "Dashboard" tab, the "Holdings %" graph present stats regarding ALL the position i ever had in my portfolio, since in the past i bough/sold many position that i dont hold anymore, the graph is cluttered with a lot of 0% position.

Is there a way to display only the "current holdings" ?

2. is there a way to override the Symbol information taken from the "cloud" ? For example VXUS name is not "Vanguard STAR Funds Vanguard Total International Stock ETF" but really is "Vanguard Total International Stock ETF"

3. Should only my "current holdings" be defined in my Web Workbooks ? From what i understand this is only used to specify wich stock stock quotes to download, right ?

Best regards :)
 
#42 ·
1. I am surprised that in Holdings % you see previous holdings. Somehow for you default filter was removed. To add filter back: on the same worksheets move cursor to cell BA:14. This is symbol values pivot table used for that chart. You need to add filter to this pivot table - click on arrow near "Row Labels" then "Value Filters" then "Greater than...". Then Choose condition "Total Value" "is greater than" 0.
2. To update any info in PS Cloud database just let me know - I'll do this for you manually. VXUS was just updated.
3. Web workbook should have a list of symbols that were used in your transactions at any point in time. You are right that PS Cloud is used just to get quotes and exchange rates, but that information is re-downloaded every time you sync. So to properly show your account value changes you have to have historical quotes for all symbols. So again, all your symbols should be in Web Workbook, even ones you just traded 5 years ago.

I know how much effort is to enter all your transaction data. I have 8yr+ data with 3800+ total transactions. But then with all history PS reports looks good :)
If you will have any suggestions for reports - please let me know. I already applied most of your rename suggestions on my development version - so this will be in the next release.
 
#45 · (Edited)
1. I am surprised that in Holdings % you see previous holdings. Somehow for you default filter was removed. To add filter back: on the same worksheets move cursor to cell BA:14. This is symbol values pivot table used for that chart. You need to add filter to this pivot table - click on arrow near "Row Labels" then "Value Filters" then "Greater than...". Then Choose condition "Total Value" "is greater than" 0.
Not sure i understand your directive, when you say "worksheets" you mean the "Dashboard" ? Also not sure what is cell BA:14, for me columns are denominated A,B,C,etc. there is no BA.

always amazed by your support vidm :)

edit: another symbol to modify when you have the chance, HBB.TO

current: HORIZONS CDN SELECT UNIVERSE BO

should be: Horizons CDN Select Universe Bond ETF
 
#43 ·
I have a simple suggestions for your next version - make the stock quantity for a sale an absolute value (positive or negative) Since a sale is always intended to be a sale and some reports use negative quantities, that would avoid a simple miscalculation

I've been on the road with a Mac so I haven't been able to look at it for awhile now, but I hadn't figured out how the "aliases" work. I figured it was to change the name of holding for the graphs but I see you are doing that for larry here manually. What are the aliases for?
 
#44 ·
Aliases work just with entering transactions and they have no impact on any of displayed reports. Aliases allows you to use any other string when you enter transactions. So instead of entering MSFT, you can create alias that makes "Microsoft" as "MSFT" and then when you enter transactions you can use "Microsoft" instead of symbol.

I need clarification regarding "make the stock quantity for a sale an absolute value (positive or negative)". When you enter transactions, for sales you already enter absolute value. In my reports sales reports also quantities and amounts are absolute. Can you please clarify this change - which report?
 
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top