[larry81]

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

 

[vidm]

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.

 

[m3s]

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?

 

[vidm]

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?

 

[larry81]

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

 

[vidm]

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

Yes, it is "Dashboard" worksheet. If you start moving to right side, you will see pivot tables used for charts. After Z column Excel starts using AA, AB, etc. So you need to go far right till column BA. You will see pivot table there.

HBB.TO name was fixed.

 

[larry81]

edit: fixed !

 

[m3s]

Ahh, so aliases could have been useful. What I did is "replace all" instead

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?

My data had all the sell transactions with a negative quantity of stocks. This threw my reports out of whack on portfolio slicer but I quickly found that and removed the negative signs. However, a sale is always intended to be a sale (it's never a buy as you would use buy transaction) So I mean you could accept + or - stock quantity for sell

Just fired up my PC again and happy to have this workbook back (even if my portfolio has slipped back lately )

 

[vidm]

Ahh, so aliases could have been useful. What I did is "replace all" instead

My data had all the sell transactions with a negative quantity of stocks. This threw my reports out of whack on portfolio slicer but I quickly found that and removed the negative signs. However, a sale is always intended to be a sale (it's never a buy as you would use buy transaction) So I mean you could accept + or - stock quantity for sell

Just fired up my PC again and happy to have this workbook back (even if my portfolio has slipped back lately )

Hi M3s,

OK - I understood now what you meant. I just added this logic to my development version of Portfolio Slicer - for "Sell" type of transactions quantity sign will be ignored. Thank you for good suggestion!

You picked bad time to check your portfolio - this month was brutal , but I believe everyone was expecting some pullback, hopefully before another big jump up...

 

[larry81]

Just updated to 0.90 without any hassle

I used to have some DFA funds, the SymbolName appear as red in my srcTrans tab. How and where should i add these ticker to be recognised ?

 

[vidm]

Have you migrated symbol alias table? That is most likely reason.

 

[larry81]

Have you migrated symbol alias table? That is most likely reason.

i added them in my symbol table, do i also need to add them in my portfolio on the website ? They are not listed on yahoo finance and dont have fixed price so i am not sure how...

 

[vidm]

Just updated to 0.90 without any hassle

I used to have some DFA funds, the SymbolName appear as red in my srcTrans tab. How and where should i add these ticker to be recognised ?

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)...

 

[larry81]

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) ?

 

[vidm]

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) ?

Please add symbols DFA600.TO, DFA391.TO and DFA231.TO to your web workbook and then you can use them in Excel workbook. I have up to date quotes for these symbols.

Regards,

 

[larry81]

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

 

[vidm]

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:

 

[larry81]

sound good but where do you use the BankExchRate in this ?

 

[vidm]

sound good but where do you use the BankExchRate in this ?

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).

 

[larry81]

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