[larry81]

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

 

[vidm]

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.

 

[larry81]

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)

 

[vidm]

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!

 

[larry81]

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

 

[vidm]

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

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.

 

[larry81]

Another quick one VIDM and then i stop bugging you

Can you give me an example on how to enter Stock split transaction ?

6/23/2011 StkSplit MIP123 10,069.929:100,699.29

Basically its a 1:10 conversion...

Not sur how to enter this ...

 

[vidm]

Another quick one VIDM and then i stop bugging you

Can you give me an example on how to enter Stock split transaction ?

6/23/2011 StkSplit MIP123 10,069.929:100,699.29

Basically its a 1:10 conversion...

Not sur how to enter this ...

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.

 

[larry81]

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

 

[vidm]

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?

 

[larry81]

I mean PS (PortfolioSlicer), sorry for the typo

I was referring to the field type used in excel to display the data, imho, for cash values, decimal should be displayed

Re: Split, it work thanks a millions

 

[vidm]

I mean PS (PortfolioSlicer), sorry for the typo

I was referring to the field type used in excel to display the data, imho, for cash values, decimal should be displayed

Re: Split, it work thanks a millions

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.

 

[larry81]

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.

 

[vidm]

Sorry for late reply - I am just back from vacation.
How would you envision using data from this column? Would you be OK with new report that would show transaction records when my internally calculated cash value does not match cash specified by user in that column?

 

[larry81]

Sorry for late reply - I am just back from vacation.
How would you envision using data from this column? Would you be OK with new report that would show transaction records when my internally calculated cash value does not match cash specified by user in that column?

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:



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.

 

[vidm]

Larry81,

Thank you - I understand now what you want. That is you want to see cash balance WHILE entering source transactions. I agree - that would be very useful. I'll investigate if I can do that in Excel table.

Thanks for suggestion!

 

[larry81]

vidm, any idea when the next update will be released ?

 

[vidm]

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!

 

[larry81]

thanks a lot vidm, i will try it tonight !!!

 

[larry81]

The modification work like a charm, would make my life much more easier

Thanks again !