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.
 
#53 ·
I need clarification - do you have this problem since you migrated to version 0.9.0, or you are adding new data about DFA funds?

If you started to experience problem just after migrating to version 0.9.0:
There are no changes to backend data, so you should be able to get the same quotes as before. If you are not, most likely reason is with SymbolAlias table - you have to copy it from old version.

If you are starting to add new symbols, but quotes are not there:
First I have to make sure that quotes cannot be obtained. So can you please post here or send me directly list of quotes for your DFA funds.
If I'll not be able to get quotes, then you either not going to be able to add symbols to PS web workbook, or you can add them as private symbols. But private symbols have fixed quote, so probably that is not an option for you.

I am slow with my replies - I am spending this week in Seattle (Microsoft)...
 
#54 ·
Thanks for the reply vidm,

I always had problem with DFA funds, this is not related to the 0.90 update, here are the tickers:

DFA600
DFA391
DFA231

The funds were sold couple years ago, could i simply add them with a fictive fixed quote (ex: price i sold them) ?
 
#56 · (Edited)
vidm i performed a norbert gambit transaction yesterday and i am not sure how to enter it properly:

in my TDDI:
2014-12-03, TD.to, BUY, 614, 56,92$, -9,99$, -34 958,87$
2014-12-03, TD, SELL, -614, 50,062$, -9,99$, 30 727,89$

in my portfolioslicer:
CAD 2014-12-03 Buy TD.TO 614.00 56.92 9.99 34958.87
CAD 2014-12-03 SymbolTransferOut TD.TO 614.00 56.94 34958.87
???
???

I did look at the FAQ, it say to use the following formula for transaction #3
TransPrice=TransTotalAmnt(PrevTransaction)*BankExchRate/TransQty and
TBookValueOverride=TotalTransAmnt(PrevTransaction)*BankExchRate.

What kind of operation is TransTotalAmnt(PrevTransaction) ??? Could you provide a working example based on my transaction ?

Also, in my detailled TDDI transaction slip, i see that i was charged 0.68$ of US taxes for the SELL operation, how do i handle this in portfolioslicer, do i simply adjust the TD stock price of the SELL operation ?

Best regards :)
 
#57 · (Edited)
Hi Larry81,

Now my assumption is that you bought in account CAD and then transferred it into account USD

Account Date Trans Symbol Qty Price Fee BookValue Override Amount
AccntCAD 12/3/2014 Buy TD.TO 614 56.92 9.99 34958.87
AccntCAD 12/3/2014 SymbolTransferOut TD.TO 614 56.93627036 34958.87
AccntUSD 12/3/2014 SymbolTransferIn TD 614 50.0628 30738.5592 30738.5592
AccntUSD 12/3/2014 Sell TD 614 50.0628 10.67 30738.5592 30727.8892

I added 0.68 to sell fee, so instead of 9.99 it became 10.67.
For symbol transfer into price I included fee, so total transfer amount matches amount that was paid.
You specify sell price for 50.062, but really I had to calculate it more precise to get sales amount 30727.89 after all expenses. So my calcs shows that price was supposed to be 50.0628 (just simple excel magic)
BookValueOverride was amount you got before subtracting fees you paid.

Image: Text Font Line Number Design
 
#59 ·
BankExchRate plays bigger role in case when you are doing this transaction in single account - first example in FAQ.
As you bough in CAD account and then transferred symbol into USD account and sold right away, your bank provided with actual price for sale.

The goal of these transactions really is:
- For CAD account your buy and Transfer out "TotalAmount" should be exactly the same. So there is really no capital gain for this transaction.
- For USD account your Transfer In value for "TotalAmount" will be in USD currency and it should be equal to CAD account book value converted to USD currency using that days exchange rate. If you are selling this symbol same day, you can then use sales price (adjusted for transaction fees).
 
#60 ·
Regarding distribution, should i enter the Ex-dividend date, the Record date or Payment date in Portfolio slicer ?

For example, VCE latest distribution

Ex-dividend date Record date Payment date Cash distribution per unit Reinvested distribution per unit Total distribution per unit
24-12-2014 30-12-2014 05-01-2015 $0.18935 $0.00000 $0.18935
 
#61 ·
In my workbook I always enter Payment date. I do this to match my bank statements with my own generated statements. Side effect is that dividends in your example will be assigned and reported in year 2015. But because I do this consistently year over year, my 2014 dividends include some of the 2013 data and so on.
You could use record date or ex-dividend date and assign distribution to proper month/year, but that would be more work for you - you would have to track each distribution and find these dates.
 
#63 ·
Hi Vidm,

Will portfolio slicer work with Microsoft Office Enterprise Excel 2007.

I wanted to know before I register and set up my portfolio

Thanks for your great efforts.

apatel
Hi Apatel,

No, Sorry, Portfolio Slicer will not work with Excel 2007 as it needs PowerPivot add-in that was introduced just in Excel 2010 and then later integrated into Excel 2013.
 
#67 ·
I probably would add this as TransType = 'Deposit', TransSubType = 'Grant'. Alternative would be to add it as TransType = 'DivTA', TransSubType='Grant', but then your dividends would be affected.
In any way you will have to create your own report (Pivot Table) that shows data by TransSubType so you would know what deposits you made and what part was grant.
 
#70 ·
vidm not sure if you still monitor this thread but i have a question regarding recent activity in my non-reg account:

Transaction date: 2015-03-09
Settlement date: 2015-01-06
Description: VANGUARD FTSE CDA IDX ETF
Type: CG DIV
Units: -17 234
Price:5 081,62$
Comm.: 0$

Bascily, its a Capital Gain Dividend, should lower my ACB, how should i enter this in portfolioslicer ?

Best regards :)
 
#71 ·
Hi Larry,

In Portfolio Slicer there are 2 transaction types to deal with such distributions:

NotionalDistrib Notional Distribution - Capital Gains Increases Book value!
ReturnOfCapital Return of Capital - Decreases book value!

So you simply enter distribution transaction with type "ReturnOfCapital", Qty=17234 and Price:5081.62
 
#73 · (Edited)
Hi Larry,

I am reviewing my own investments and I also see for XEI "CG DIV" transaction. I believe that these "CG DIV" transactions should "Increase" (!!!!) your ACB, so you should be using NotionalDistrib transaction type.

Here is my look at that:

Let say I invested in XEI stocks. During the year I receive dividends into my account (assuming regular account, NOT RRSP or TFSA), lets say 100$/month. Around March of next year I receive "Summary of Trust Income" from my bank that now tells me that every month (actually values every month could be different) I received:
90$ of "Total Capital gains, box 21"
8$ of "Actual Amount of eligible dividends, box 49"
2$ of "Return of Capital, box 42"
As I receive dividends every month I know that my "Return of Capital" for year is 24$ (12x2$).
For this in portfolio slicer I create 2 transactions for the last day of the year, or if stock was sold during the year, then I enter transactions one day before last sale.
Symbol: XEI, TransType: DivTA, TransSubType: DivTypeAdj, Qty: 1000, Price: -24$
Symbol: XEI, TransType: ReturnOfCapital, TransSubType: DivTypeAdj, Qty: 1000, Price: 24$
So with these 2 transactions I subtracted 24$ from general dividends paid by that symbol and then added 24$ for "Return of Capital" paid by that symbol. Total paid dividend stays the same.
With these transaction ACB will be reduced and that means when I will sell that symbol, my Capital Gain will be bigger.

Some symbols have NotionalDistributions that are "Reinvested distributions". For example symbol XEI on Dec 29, 2014 paid 0.74306$ per share as "Reinvested Distributions". In my TD Waterhouse statement I see this as "CG DIV" transaction followed by "DRIP" transaction. Other banks might not post any transaction for such distribution. In Portfolio Slicer I enter this as one transaction:
Symbol: XEI, TransType: NotionalDistrib, Qty: 1000, Price: 743.06$
This distribution is not really paid out as you do not see any change in your cash values.
This transaction increases ACB by distribution amount and that means when you will sell symbol, your capital gain will be smaller.
Please note that info about this distribution is not included in your "Summary of Trust Income" as separate transaction type, so really if you did not properly identify this transaction and adjust your ACB, you will pay extra tax. But if you carefully review "Summary of Trust Income" usually at the month of December you will see distribution paid that is bigger than amount you received in your account. Please note that if you carefully check T3, you will see that it includes that extra distribution. So really with Notional Distributions you pay Cap Gain tax in the year you receive it and then pay less tax when you sell your symbol.

So please double check what type of distribution is this so that you do not overpay tax!!

Regarding next Portfolio Slicer version - I am working on it every day, I just do not want to rush releasing it before I am happy with functionality. I am not sure when next release will be done.
 
#75 ·
In Portfolio Slicer cloud database stock quotes are refreshed few times per day - mostly to accommodate different time zones (many users are from Europe, Asia) and to capture general days trend. Mutual fund prices are updates once a day - 5 hours after market close. Quote refresh time cannot be modified. Portfolio Slicer is designed for reporting on your investment performance, so having few hours delayed quotes should be sufficient for absolute majority of people.
 
#76 ·
I'm still using it regularly and it's pretty much everything I wanted Quicken to be.

On the dashboard, one of my "top YTD winners" seems to be wrong, possibly counting gains from the book value or something (I repurchased it this year, shows as a YTD winner +24% but it's actually down slightly this year)

When I reconciled the balances I also found that I had input some bank fees backwards (negative) Not sure if you could have a warning for that somehow but it's not a big deal

I like the buttons for CAD/USD/original and the exchange rate impact etc. All the tabs are great insight as well and easy to use. :encouragement:
 
#79 ·
I'm still using it regularly and it's pretty much everything I wanted Quicken to be.
On the dashboard, one of my "top YTD winners" seems to be wrong, possibly counting gains from the book value or something (I repurchased it this year, shows as a YTD winner +24% but it's actually down slightly this year)
When I reconciled the balances I also found that I had input some bank fees backwards (negative) Not sure if you could have a warning for that somehow but it's not a big deal
I like the buttons for CAD/USD/original and the exchange rate impact etc. All the tabs are great insight as well and easy to use. :encouragement:
Hi m3s, I believe that issue with top YTD winners will be fixed in the next release - I changed how Profit % is calculated and that affects YTD winner. I also added option not to track cash, so you will not need to do deposits before buying stocks - and that should simplify PS usage substantially.

Regards,
 
#77 ·
i'm trying this but find documentation seems a bit lagging and does not match current version. Can someone explain what's "account" and "portfolio" ? On existing demo and old version there is only portfolio which seems easier to handle. Not sure how I handle account vs. portfolio in the new version when I enter transactions. Thanks !
 
#78 ·
Account usually matches your bank account.
Portoflio - group of accounts that you group any way you want. For example you might use 2 values in portfolio - one your name and another your spouse name. Portfolio can be used to apply filter to report, for example you want to see just accounts that belong to you and not to your spouse.
 
#82 ·
vidm it would be nice if the CAD-USD exchange rate could be updated during the day. As someone who happens to look at his portfolio in the AM and PM, it would be nice for the exchange rate to update automatically as the rate change. At the end of the day you could simply refresh with the end-of-the-day value.

portfolioslicer is the best thing since sliced bread ! It replaced my quicken entirely.
 
#85 ·
Still have questions. Sorry.

For RBC RRSP account, we have both US and CAD account to buy US and canadian stocks. Do I need to set up 2 accounts in PS to track them, or shall I handle them in one account with CAD currency ? probably two accounts so easier to track currency ? If two accounts how do I handle Norbert's Gambit transaction between the two accounts ? 2nd example in the FAQ below seems to explain this ?

http://portfolioslicer.com/Support/FAQ#norbert
 
#86 ·
Still have not figured out how to put some existing stocks into my account without going through cash deposit and buy process. I tried to deposit same amount of cash as the stock market value on Dec 31 2014 and put another buy transaction on same day. Somehow it just does not show up in the holdings tab. not sure what I did wrong. Would appreciate some help from vidm. Thanks
 
#88 ·
Thanks so much, Vidm. I kind of figured out this similar to your suggestions. Basically I deposite some cash end of 2014 and then enter some buy transaction Jan 1 2015 for previous holdings. It now works great.

Yes I also created separate account for RRSP as one holds CAD stocks and the other US stocks in two currencies.

I have to say that this is an awesome tool and will help me a great deal in tracking my investment. I was doing some of these in Excel myself but it's no where close to what I can get with PS tool. Thanks for creating and sharing this !
 
#89 · (Edited)
Hi Vidm: spent hours to try to get the above to work, i.e., deposit cash and enter buy transaction using same amount of cash so that I can record previous stocks bought. Unfortunately doing that somehow screw up the cash holding calculation. In the srcTran tab the Totalcashbalance is correct, but in the holding tab it gave me less cash and not sure why. If I removed these deposit and buy transactions I get correct cash holdings. If i deposit arbiturary amount of cash to make cash holding correct, then the srcTran tab will show incorrect number ? I hope there is an easy way around this problem as I think this might happen quite often. Thanks,

Seems found the issue. The reduction of cash is because cash deposit for previous stocks is done in 2014 Dec 30. If I change deposit date to Jan 1 2015 I get correct number now.
 
#90 ·
I spent hours trying to get it working. I can't get the right amount of total amount shown on dashboard. I've copy and pasted transactions from my brokerage account and the quote it grabbed from cloud is correct. So I don't know where it went wrong yet

Also I have an error every time I "refresh all" in Data tab. "A PowerTable Report cannot overlap another PivotTable report."

This error shows up even before I touch anything after downloading it

Any idea? Thanks.
 
#91 ·
Your numbers are not right because you cannot refresh data. So we have to first fix that issue.
If you are getting this error message right after downloading, my guess is that your workbook minimum date that was setup on Portfolio Website is not right. Please check it as the first step. Maybe it is today's date, maybe it is future date?
After you confirmed that this date is correct you are ready to review reports and reset filters to proper value.
Excel reports are displayed through Pivot Tables. These Pivot Tables cannot overlap. Most of these Pivot Tables have filters applied to them so they are limited how big they are. For example instead of showing all days, Pivot report migh show just data for last 20 days. Filters are just above Pivot Table and looks like "Days-Last20 Yes", "Years-Current Yes", "Mths-Last 13 Yes". If you setup incorrect minimum Portfolio Slicer date, then maybe there was no date records and these filters where automatically reset to value "All" instead of "Yes". So after filter reset your report might now show 365 days instead of 20, so report is much bigger and it overlaps another report. So to fix issue you have to review each report and make sure that each Report filter (if exists) is setup to value "Yes". You have to check that in each worksheet and if worksheet has chart, it has related Pivot Table report that will be out of main screen far right.

If you still have issue, you can send me workbook and I can investigate.
 
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