Canadian Money Forum banner
1 - 20 of 65 Posts

·
Registered
Joined
·
86 Posts
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.
 

·
Registered
Joined
·
86 Posts
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
Portfolio Slicer does not work with MAC because MAC version of Excel does not support PowerPivot - engine that is used for all calculations. So it is really out of my hands - but I do hope that Microsoft will include PowerPivot support in next version of Excel for Mac.
 

·
Registered
Joined
·
86 Posts
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 !
Hi Larry,

I added FAQ entry on how to enter Norbert's Gambit transactions into Portfolio Slicer: http://portfolioslicer.com/Support/FAQ#norbert
If you have any problems - please do not hesitate to ask!
 

·
Registered
Joined
·
86 Posts
I don't normally keep track of cash in or out... excluding tracking of Dividends, will portfolio slicer have any hiccups if deposits or withdrawals are completely omitted?
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,
 

·
Registered
Joined
·
86 Posts
Thanks! Just wondering, what IF these are omitted - what exactly doesn't get tracked/reported properly?
If you would not use these special transaction types and would not add cash deposits/withdrawals you would see incorrect "Cash Value" and "Total Value" for Portfolios and for Total reports. So lets say you buy MSFT for 100,000$. Your Cash Value for that portfolio would be -100,000$ and your total value for that Portfolio would be 0: (CashValue + StockValue) = (-100,000 + 100,000) =0. Of course you can add all your "Buy" transactions and Portfolio Slicer will calculate that Cash Value is -x$. Then you just go back and enter source deposit transaction for x$, then your CashValue and Stock value will be OK, but Portfolio Rate of return could be affected (deposited Jan 1st x amount, bought first stock on Jan 2nd, then second stock in Nov 2nd, so PS will assume that cash was sitting in account and not earning much).
 

·
Registered
Joined
·
86 Posts
Quicken usually has hundreds of fixes with every new release and it has thousands of users. I can't imagine how fixes this product will have, considering so few are using it now --- I won't be the guinea pig here :)
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.
 

·
Registered
Joined
·
86 Posts
Not sure i understand, i have both a US and a Canadian account, isnt something that should be configured in Portfolio Slicer ? Can you do an example of Norbert Gambit with the an interlisted stock (Ex: TD) ? Just want to avoid any confusion :)
Larry - You are right, I assumed that you are looking to do Norbert Gambit in the same account. But of course there could be different usage when you buy in one account and sell in another. I just updated same FAQ entry and added another example what you should do when you are buying in one and selling in another account. Please visit same page: http://portfolioslicer.com/Support/FAQ#norbert

And thank you for good question - I am sure this FAQ entry will help many people.
 

·
Registered
Joined
·
86 Posts
I've used Quicken for years. I've always felt it leaves much to be desired when it comes to investments. It tracks them well, but it doesn't give you much of a complete picture. I had to use a lot of custom reports to get what I needed.

It did take me a few days to setup this Portfolio Slicer. You register online, add all your tickers online, get power pivot setup on excel and connected (there's great YouTube instructions for this) then export Quicken data to excel and convert and rearrange it all to this format ("replace all" function makes this step much less tedious) Negative stock quantity for a sale won't work, but that could be fixed (regardless of + or - a sale is not confused with a buy)

Portfolio Slicer definitely presents a much better look at my portfolio. I feel like Quicken was made by programmers who don't buy stocks while portfolio slicer has exactly what an investor wants. It's very well done imo plus it's FREE!
M3s - thank you very much for nice comments about Portfolio Slicer - I really appreciate them!!
 

·
Registered
Joined
·
86 Posts
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,
 

·
Registered
Joined
·
86 Posts
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.
 

·
Registered
Joined
·
86 Posts
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!
 

·
Registered
Joined
·
86 Posts
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.
 

·
Registered
Joined
·
86 Posts
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.
 

·
Registered
Joined
·
86 Posts
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?
 

·
Registered
Joined
·
86 Posts
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.
 

·
Registered
Joined
·
86 Posts
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?
 

·
Registered
Joined
·
86 Posts
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!
 

·
Registered
Joined
·
86 Posts
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.
 

·
Registered
Joined
·
86 Posts
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?
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?
 
1 - 20 of 65 Posts
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