# Portfolioslicer (Automated Excel portfolio tracker)



## larry81

http://portfolioslicer.com

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

I currently use Quicken but portfolioslicer seem much more complete.


----------



## Kaitlyn

larry81 said:


> http://portfolioslicer.com
> 
> Discovered this workbook today... anyone use it ? It look very nice !
> 
> I currently use Quicken but portfolioslicer seem much more complete.


It is nice but I don't like that it seems to require the central server for the core data. Fine that it's free and around, but are you only planning to track investments this year? I'm wondering and worried about 5-20 years down the road!


----------



## larry81

Kaitlyn said:


> It is nice but I don't like that it seems to require the central server for the core data. Fine that it's free and around, but are you only planning to track investments this year? I'm wondering and worried about 5-20 years down the road!


Central server is only to pull stock quotes, but yes your concern is valid.

Too bad Excel stock quote integration is piss poor.

Google spreadsheet googlefinance() function is magical !


----------



## vidm

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.


----------



## fatcat

vidm said:


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


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


----------



## vidm

fatcat said:


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


----------



## larry81

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 !


----------



## vidm

larry81 said:


> 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!


----------



## Kaitlyn

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?


----------



## vidm

Kaitlyn said:


> 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,


----------



## Kaitlyn

vidm said:


> 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,


Thanks! Just wondering, what IF these are omitted - what exactly doesn't get tracked/reported properly?


----------



## vidm

Kaitlyn said:


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


----------



## webber22

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


----------



## vidm

webber22 said:


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


----------



## larry81

vidm said:


> 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!


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


----------



## m3s

webber22 said:


> 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


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!


----------



## vidm

larry81 said:


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


----------



## vidm

m3s said:


> 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!!


----------



## larry81

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 ?


----------



## vidm

larry81 said:


> 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,


----------



## 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

larry81 said:


> 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

larry81 said:


> 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

larry81 said:


> 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

vidm said:


> 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 !


----------



## 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

m3s said:


> 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

vidm said:


> 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

larry81 said:


> 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



vidm said:


> 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

m3s said:


> 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

vidm said:


> 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

larry81 said:


> 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

larry81 said:


> 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

larry81 said:


> 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


----------



## vidm

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.


----------



## apatel

*Will it work with Excel 2007??*

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


----------



## vidm

apatel said:


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


----------



## larry81

Whats in for the next update vidm ? 

Any big plan for a 1.0 release ?


----------



## larry81

vidm, regarding RESP tracking in portfolioslicer, what TransType/TransSubType would you use for government grant ?


----------



## vidm

larry81 said:


> Whats in for the next update vidm ?
> 
> Any big plan for a 1.0 release ?


I am not sure yet, there might be 0.9.9 release before 1.0. But I am close to having release that has features that I believe must be present in Portfolio Slicer. I am working on it every day. There will be option not to track cash and help with dividends .


----------



## vidm

larry81 said:


> vidm, regarding RESP tracking in portfolioslicer, what TransType/TransSubType would you use for government grant ?


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.


----------



## apatel

vidm said:


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


Thanks vidm, I am planning to buy a new laptop. I will then be able to use this

apatel


----------



## larry81

Portfolioslicer is DIY investor best friend !

I look at my dashboard daily


----------



## larry81

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


----------



## vidm

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


----------



## larry81

thanks vidm !!! Any plan for the next update ?


----------



## vidm

larry81 said:


> thanks vidm !!! Any plan for the next update ?


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.


----------



## chinamank

Excel on steroids. Pretty slick. 
With your server running in the cloud are you having any issues with quote refresh latency? 
Can the quote refresh times be modified at all?


----------



## vidm

chinamank said:


> Excel on steroids. Pretty slick.
> With your server running in the cloud are you having any issues with quote refresh latency?
> Can the quote refresh times be modified at all?


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.


----------



## m3s

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:


----------



## GGO

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 !


----------



## vidm

GGO said:


> 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 !


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.


----------



## vidm

m3s said:


> 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,


----------



## GGO

vidm said:


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


Thanks very much. I spent some time last night to go through the steps. Got most working but today I can't seem to refresh/update as I always get user name/password error when I try to refresh data in power pivot table. Not sure why as it seems to work yesterday. Also got warning about 32 bit version of Excel 2010 which is the one I'm using.


----------



## vidm

GGO said:


> Thanks very much. I spent some time last night to go through the steps. Got most working but today I can't seem to refresh/update as I always get user name/password error when I try to refresh data in power pivot table. Not sure why as it seems to work yesterday. Also got warning about 32 bit version of Excel 2010 which is the one I'm using.


Regarding username /password error.
As on the same PC you were able update yesterday, I have to assume that your PC is setup correctly and you have SQL Server Native Client installed.
Second most common reason for this error - when you are running some firewall on your PC, strange thing is that in such case I have to assume you had firewall yesterday and it worked. 
I had users reported same issue, and then reported back that they had this issue when they run VPN on their PC. Maybe that is your case?

If you still experience this issue, please let me know, I'll post what you should do next.


----------



## larry81

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.


----------



## vidm

larry81 said:


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


Larry81 - Thanks! I added your suggestion about exchange rate more frequent updates. But I cannot promise anything in the near future, sorry...


----------



## GGO

vidm said:


> Regarding username /password error.
> As on the same PC you were able update yesterday, I have to assume that your PC is setup correctly and you have SQL Server Native Client installed.
> Second most common reason for this error - when you are running some firewall on your PC, strange thing is that in such case I have to assume you had firewall yesterday and it worked.
> I had users reported same issue, and then reported back that they had this issue when they run VPN on their PC. Maybe that is your case?
> 
> If you still experience this issue, please let me know, I'll post what you should do next.


You are right it is due to firewall. Now it is working fine.

I do have another question: in my case I started investment mainly this year, so I plan to track from begining of this year. I do have some stocks/mutual funds prior to this year. Is there a way to just take a snapshot of those previous investment, and only track buy and sell starting this year ? e.g., if I use transaction Deposit, can I deposit a stock instead of cash ? Wonder what's the best way to handle that. Thanks again.

I figured it out. Used Buy+Deposit. Still playing. Looks like an awesome tool.


----------



## GGO

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


----------



## GGO

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


----------



## vidm

GGO said:


> You are right it is due to firewall. Now it is working fine.
> 
> I do have another question: in my case I started investment mainly this year, so I plan to track from begining of this year. I do have some stocks/mutual funds prior to this year. Is there a way to just take a snapshot of those previous investment, and only track buy and sell starting this year ? e.g., if I use transaction Deposit, can I deposit a stock instead of cash ? Wonder what's the best way to handle that. Thanks again.
> 
> I figured it out. Used Buy+Deposit. Still playing. Looks like an awesome tool.


Actually for initial setup recommendation is:
- Make sure your web workbook minimum date is setup to 2014-12-30.
- Make deposit transaction into account that is in amount of total book value. Use date 2014-12-30 for that deposit transaction
- Make buy transaction with date 2014-12-31 with original price. Data for 2014 will show big cap gain/loss, but you are going to look just into 2015 data.

Q: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 ?

A: I make one account in PS per each account in your bank institution. It is very important what is PS account currency - all transactions in that account are assumed to be made in the PS account currency! So if you have 2 accounts, you want to create 2 accounts in PS with correct currency. Yes, for Norbert Gambit I have FAQ that explains how to do this.

Q: 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. 

A: At this point you have to first deposit money into account and then enter buy transactions. I explained above how to deposit one day before buy transactions. I suspect that you might have issues because your minimum workbook date is 2015-01-01 and not 2014-12-30. If you do not know amount you need to deposit first, then deposit 1mln $, do all your buy and then check "Cash Balance", lets say after last buy transaction it shows 912,456$. Then you do simple math: 1,000,000-912,456=87,544 and then you simply replace original 1mln deposit value with 87544$ and that means your last cash balance now will be 0$.

I realize that PS does not have enough documentation, I am adding more comments and samples to new release I am working on. My highest priority is to make necessary reports and then I'll work on more documentation.


----------



## GGO

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 !


----------



## GGO

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.


----------



## mf4361

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.


----------



## vidm

mf4361 said:


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


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.


----------



## vidm

GGO said:


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


Please check your minimum workbook date you setup through website. It is very important date - if you enter any transactions before that date, these transactions might get ignored.


----------



## GGO

vidm said:


> Please check your minimum workbook date you setup through website. It is very important date - if you enter any transactions before that date, these transactions might get ignored.


Thanks Vidm. Slowly learned my way. Seems everything ok now, and holdings in my bank is matching PS result. This is a great tool to track investment.


----------



## mf4361

vidm said:


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


I've double checked and turns out the wrong calculation of the total worth is due to a small typo of the "dot" in a symbol is a dash here. So I put CGL.C.TO instead of CGL-C.TO and PS overlooked the transaction.

I've checked Min Date on PS Web is correct. But then after I refresh the download link and try it again, and everything seems to be fine now. Thanks so much for your help.

One thing I noticed is the first 2 lines on srcTrans is always 10/24/2014, where it should have been the min date. If the "downloaded workbook" can set these two fields according to Min Date users input on web that would be better. (Maybe that's where I messed up before)

This Excel-based book is tapping the 90% of Excel features that normal users have no idea about. Great work.


----------



## m3s

vidm said:


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


Is there a way for me to see the formulas? I used to be an excel wiz years ago.. but never used these pivot tables. Some of the cap gains in the yearly tab are way off, but the same holdings seem correct in the holdings tab etc



vidm said:


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


No kidding! I just reconciled 5 years worth of transactions across several accounts to within pennies. I guess without cash you just wouldn't get a cash allocation or accurate stats on fees.


----------



## vidm

m3s said:


> Is there a way for me to see the formulas? I used to be an excel wiz years ago.. but never used these pivot tables. Some of the cap gains in the yearly tab are way off, but the same holdings seem correct in the holdings tab etc
> 
> No kidding! I just reconciled 5 years worth of transactions across several accounts to within pennies. I guess without cash you just wouldn't get a cash allocation or accurate stats on fees.



Cash or no cash tracking will be an option that you can choose. I am tracking cash myself, because there are times when I have some money in cash and I want to be included that in my reports. But tracking cash makes PS usage a bit harder - users have to deposit money before buying and that make system a bit more complex. So having an option to start PS without tracking cash, getting to know system and then turning cash tracking later might be good option for many users.
*
Almost all formulas used in PS calculations are open and available for you to see and change* . I have few users that adjusted system to their likings and contributed me with some formula suggestions 

In Excel 2010 click on "PowerPivot Window", then some calculations are defined in calculated column (visible in excel formula bar when you select any cell in that column) and others are defined as calculated measures (Visible at the bottom part of the screen for each table). You can also see calculated measures by selecting measure from "PowerPivot field list" on the right (measure will have calculator icon on the right side) and then choosing "Edit Formula".

In Excel 2013 these are similar steps in PowerPivot window and from Excel interface you need to select PowerPivot menu and choose "Calculated Fields" button to edit measures. You can see/change formulas just when you have Excel 2013 Professional Plus Edition.

If you find any issue with Capital Gains calculations, please let me know so I can include fix into next release. You can also delete data and leave just one symbol transactions and send me workbook and I can review calculations to figure out issue.


----------



## m3s

So the formulas are written all in DAX? I was expecting more traditional excel mathematical formulas from years ago, or maybe I'm still looking in the wrong place

If it's all in DAX, I'll be lucky if I can figure out what is being calculated and if something is wrong. It looks like other languages, but different enough that I probably won't be much help with solutions.

I already seem to have crashed 2013 Pro Plus just by looking at a few PowerPivot windows. I didn't do anything, honest!


----------



## vidm

m3s said:


> So the formulas are written all in DAX? I was expecting more traditional excel mathematical formulas from years ago, or maybe I'm still looking in the wrong place
> 
> If it's all in DAX, I'll be lucky if I can figure out what is being calculated and if something is wrong. It looks like other languages, but different enough that I probably won't be much help with solutions.
> 
> I already seem to have crashed 2013 Pro Plus just by looking at a few PowerPivot windows. I didn't do anything, honest!


Yes, most of the calculations are in DAX. It is very powerful language to working on data in memory. That is why I do ALL calculations on the fly. Every single cell you see in the report is calculated on the fly. For example to get Total value at any day I have to go through all transactions for each symbol, find buy (add quantity) and sells (subtract quantity) to get final quantity. Then I find last price for that symbol and then I get value for symbol. I repeat the same for each symbol, add all data and now I have Total value. 
I do not believe that you crashed PowerPivot - you simply did not wait long enough for data to come back. It might take you 1-3min just for calculations complete and there is no notice that calculations are being done - feature of Excel 2013 and the fact that we have 40+ reports and due to Excel 2013 behavior they are refreshed twice (!).
DAX and Excel languages are similar for basic stuff, but as soon as you go to more complicated modeling, it is nothing alike .
I work on Business Intelligence stuff for living, so this PowerPivot/DAX stuff is just continuation on what I was working for many years for Enterprise Customers.


----------



## m3s

vidm said:


> DAX and Excel languages are similar for basic stuff, but as soon as you go to more complicated modeling, it is nothing alike .
> I work on Business Intelligence stuff for living, so this PowerPivot/DAX stuff is just continuation on what I was working for many years for Enterprise Customers.


OK so I don't plan to write DAX anytime soon but I see kind of what you were doing :tongue: It's worse than my German but at least not Chinese

I figured out my discrepancy between the cap gains was user related... one was set to *original* and the other was not, so some looked the exact same and others were compounded by this year's growing exchange rate.

Now I suspected the discrepancy in my YTD Profit was because of the average price (I repurchased a stock this year and it was showing a big YTD profit and RoR when it is actually flat YTD) Turns out this is also currency related because *original* shows correctly!

The stock is flat since I repurchased it, and the CAD Profit does correspond to the CAD/USD exchange YTD (25%). However, I bought it on Feb 20 and the CAD/USD is pretty much flat since Feb 20.. Could it be using the exchange rate from Jan 1?

I have a reverse example of a USD stock I owned from Jan 1 and sold on Feb 24 for a 34% YTD profit. But in CAD Profit YTD it shows just 15% even though its profit in CAD should be higher? Now I'm just confusing myself.

Anyways getting really nit picky there. I really like having the currency buttons and filters and seeing how it affects everything else


----------



## vidm

m3s said:


> OK so I don't plan to write DAX anytime soon but I see kind of what you were doing :tongue: It's worse than my German but at least not Chinese
> 
> I figured out my discrepancy between the cap gains was user related... one was set to *original* and the other was not, so some looked the exact same and others were compounded by this year's growing exchange rate.
> 
> Now I suspected the discrepancy in my YTD Profit was because of the average price (I repurchased a stock this year and it was showing a big YTD profit and RoR when it is actually flat YTD) Turns out this is also currency related because *original* shows correctly!
> 
> The stock is flat since I repurchased it, and the CAD Profit does correspond to the CAD/USD exchange YTD (25%). However, I bought it on Feb 20 and the CAD/USD is pretty much flat since Feb 20.. Could it be using the exchange rate from Jan 1?
> 
> I have a reverse example of a USD stock I owned from Jan 1 and sold on Feb 24 for a 34% YTD profit. But in CAD Profit YTD it shows just 15% even though its profit in CAD should be higher? Now I'm just confusing myself.
> 
> Anyways getting really nit picky there. I really like having the currency buttons and filters and seeing how it affects everything else


Is you issue with [Profit %] or [Profit]. These are 2 very different things. I struggle with [Profit %] implementation and change calculation few times already. New release will have also adjusted calculations (very simplified). Some explanations are in FAQ (http://portfolioslicer.com/Support/FAQ) under question "How Portfolio Slicer calculates Profit %?". Basically issue is when you calculate [Profit %] you take [Profit] and divided it by something? That something could be 
a) Start value ( at the start of the year your stock was worth 100$, over the year you made 10$ profit, so [Profit %]=10/100 = 10%).
b) Ending value
c) Start value + half of external flow in/out (deposits, buys,etc).
d) something else.

None of these options will work perfectly in every situation. Lets say you calculate Profit % for current year and you might have one of these situations:
- You started (year start) with 0 and then bought large amount of stock.
- You started with 1 share and then bought 10000 shares.
- You started with 10000 shares and then slowly sold shares and have nothing at the end.
- many other scenarios when external flows have huge impact on [Profit %]
You can add another issue - as soon as you start to do a lot of IFs checking in DAX, your performance is substantially affected (by many times!).

So if your issue is with [Profit %], I probably can explain why in certain situations you see results that are not as expected. But I would have to see workbook with that symbol.
If you have [Profit] value not expected, then there is something wrong in my formulas and I would have to investigate...
There is big fix coming in next release for ACB calculation. There is no issue if you do series of buys and then one sale. But as you start to have multiple sales on the same symbol, current (0.9 release) book value is average over ALL time and next version will have proper recursive calculation for that.


----------



## GreatLaker

I installed Portfolio Slicer last week and this weekend finished by entering reinvested capital gains and notional distributions in my non-registered account so the cost base is correct. Not a great week for the markets.
*
What an fantastic spreadsheet with an amazing amount of detail. Thanks VDIM!* I like the fact that when buying or selling shares I don't have to worry about the sign... just enter the # of shares and PS knows that buys increase the quantity and sales decrease the quantity.

Couple items for VDIM:

1) On the Fees tab a trading commission (Trading Fees) that I pay displays with a positive sign, but a bank fee (Fees Admin) that I pay displays with a negative sign. The only reason I noticed is a fee rebate showed up as a positive value rather than negative.
2) Are the Account Group 1, 2 and 3 categories used? It does not look like it, maybe they are for future use or creating my own reports?
3) What is the difference between Tax Inside and Outside?
4) What's the best way to enter GICs? I created a private symbol for each with a price equal to the yearend $ value of the GIC from my broker statement (I'll only update the price at each year end.) Then entered a buy transaction with a quantity of 1 and a price equal to the original $ value of the GIC.

For other users:
A) If you have moved from Quicken to Portfolio Slicer are you still entering transactions in Quicken? I use it for all my banking, so will keep using it at least to show cash moving in and out of my brokerage account.
B) Are you tracking ACB in Portfolio Slicer? I have always used AdjustedCostBase.ca. If you have not tried it take a look.


----------



## vidm

GreatLaker said:


> I installed Portfolio Slicer last week and this weekend finished by entering reinvested capital gains and notional distributions in my non-registered account so the cost base is correct. Not a great week for the markets.
> *
> What an fantastic spreadsheet with an amazing amount of detail. Thanks VDIM!* I like the fact that when buying or selling shares I don't have to worry about the sign... just enter the # of shares and PS knows that buys increase the quantity and sales decrease the quantity.
> 
> Couple items for VDIM:
> 
> 1) On the Fees tab a trading commission (Trading Fees) that I pay displays with a positive sign, but a bank fee (Fees Admin) that I pay displays with a negative sign. The only reason I noticed is a fee rebate showed up as a positive value rather than negative.
> 2) Are the Account Group 1, 2 and 3 categories used? It does not look like it, maybe they are for future use or creating my own reports?
> 3) What is the difference between Tax Inside and Outside?
> 4) What's the best way to enter GICs? I created a private symbol for each with a price equal to the yearend $ value of the GIC from my broker statement (I'll only update the price at each year end.) Then entered a buy transaction with a quantity of 1 and a price equal to the original $ value of the GIC.
> 
> For other users:
> A) If you have moved from Quicken to Portfolio Slicer are you still entering transactions in Quicken? I use it for all my banking, so will keep using it at least to show cash moving in and out of my brokerage account.
> B) Are you tracking ACB in Portfolio Slicer? I have always used AdjustedCostBase.ca. If you have not tried it take a look.


Hi GreatLaker,

Thank you for such positive Portfolio Slicer review - I appreciate them very much!

Answers:
1) Negative "Fees Admin" was bug that will be fixed in next release. Thanks for reporting this issue! 
2) These are just grouping fields that are not used in any reports. But you can easily yourself these grouping fields as filter or slicer to any report.
3) There is no difference - in Demo workook values are Inside/Outside. In my own workbook values are TFSA, RRSP, Cash. I cannot use RRSP or TFSA values as many users are in all over the world and they do not know what these values means. But this is just another grouping field.
4) To enter GIC my recommendation would be: Create private symbol for each different GIC you want to track. Set private symbol value to 1. Then when you enter GIC buy transaction, specify Qty as dollar amount you buying, example 5000 and buying price as 1$. When selling you can specify one transaction for sell with sell price of 1$ and another transaction of dividends on symbol with TransType = 'DivTA', Qty=1 and Price=<interest received> If you interest is added yearly to exiting buy amount (auto-renew), then you can simply add dividend transaction for interest and then add buy transactions for same GICs with quantity equal to interet received.


Regarding ACB.

I believe that next release of Portfolio Slicer will be able much better handle ACB. All of your ACB calculation will be done instantly in Transactions table - no need to do any refresh. That is - as soon as you enter sell transaction, I'll review all your buy/sell/NotionalDistrib/ReturnOfCapital transactions and will calculate ACB for that sell transaction recursively. You will be able to also enter Exch Rate for that transaction or let PS use cloud exchange rate. As you know exch rate is critical in properly calculating ACB for foreign holdings. Adding ability override default Exch Rate helps too - for example most likely you will enter ReturnOfCapital transaction once a year using last day of years date. But you will not want to use default Exch Rate for 2014-12-31, but instead will want to use "Years Average" exchange rate. So basically ACB calculation was moved completely into Excel (vs PowerPivot before).

Regards,


----------



## m3s

GreatLaker said:


> For other users:
> A) If you have moved from Quicken to Portfolio Slicer are you still entering transactions in Quicken? I use it for all my banking, so will keep using it at least to show cash moving in and out of my brokerage account.


I'm still using Quicken since it holds so many past transactions and I can download new ones in Quicken format. I have created and saved Quicken reports for each account to export data to excel, then I just have to rearrange some columns and "replace all" to get the PortfolioSlicer format. Just watch the + and - for fees and sells etc. I don't intend to ever pay for Quicken again but it is useful for tracking networth and having everything in one place 

For vidm, I will reply to your last post at some point. I have a MacBook so I don't have PortfolioSlicer on the road. I just DIY upgraded it to SSD and moved the HDD into the optical drive's place so I could easily install Windows on a partition now to have the few programs that are Windows only. I think that will be the best of both worlds.. or maybe Windows 10 will knock one out of the park?


----------



## GreatLaker

Sounds like an awesome release coming up.



vidm said:


> 3) There is no difference - in Demo workook values are Inside/Outside. In my own workbook values are TFSA, RRSP, Cash. I cannot use RRSP or TFSA values as many users are in all over the world and they do not know what these values means. But this is just another grouping field.


More universal demo values for tax treatment would be Taxable (eg. Cash & Margin accounts), Tax Deferred (eg. RRSP, LIRA, IRA) and Tax Exempt (eg. TFSA, Roth IRA).

Thanks again.

Edit: one other item I noticed is the MER on iShares XEC in the cloud database is blank. iShares website shows the Management Fee as 0.25%, and the prospectus shows the MER for 2014 to be 0.27%


----------



## StockTrader

Quick question since I can't immediately find the answer; do the spreadsheets allow you to calculate money-weighted returns (sometimes called internal rate of return or dollar-weighted returns)?


----------



## zaphod

I have all of my historical transactions in Quicken. Can I export these to something like a CSV or QIF file and then import them into Portfolioslicer?

Will Portfolioslicer track my CAD ACB for USD investments in a USD based account?

Is there any easy way to send transactions from my TD Waterhouse accounts into Portfolioslicer?


----------



## larry81

zaphod said:


> I have all of my historical transactions in Quicken. Can I export these to something like a CSV or QIF file and then import them into Portfolioslicer?


You can export your transations to txt format but be prepared to do lot of search/replace to get it to the proper format.


----------



## vidm

GreatLaker said:


> Edit: one other item I noticed is the MER on iShares XEC in the cloud database is blank. iShares website shows the Management Fee as 0.25%, and the prospectus shows the MER for 2014 to be 0.27%


Thanks! I fixed XEC.TO MER. Also for future, if MER data is not correct in cloud database, you can always specify your own MER in "Edit Symbol" web page at the bottom


----------



## vidm

StockTrader said:


> Quick question since I can't immediately find the answer; do the spreadsheets allow you to calculate money-weighted returns (sometimes called internal rate of return or dollar-weighted returns)?


Portfolio Slicer allows to calculate "Internal Rate of Return" - that is same as Excels XIRR function. In Portfolio Slicer it is called "RoR Symbol" and "RoR Account". (RoR stands for Rate of Return). But this formula works just for single symbol or for accounts (1 or more). It does not work on group of symbols - for example if you selected symbols in "Real Estate" allocation. Also, other % calculations (Profit%, Cap Gain %) is calculated monthly and then geometrically linked so money flows have less impact on results.


----------



## larry81

cant wait for your next release with "ehanced" ECB tracking !


----------



## GreatLaker

vidm said:


> Thanks! I fixed XEC.TO MER. Also for future, if MER data is not correct in cloud database, you can always specify your own MER in "Edit Symbol" web page at the bottom


Thanks VDIM!

Now my annual management fee is accurate at .14% instead of .13%. Gotta love low cost ETFs. :smilet-digitalpoint


----------



## GreatLaker

I just noticed that PortfolioSlicer has been updated to version 1.0.2.
I have not installed it yet but it looks like some good improvements.

http://portfolioslicer.com/News/Release-Announcements/2015-04-24-v101-Released


----------



## larry81

Just tried migrating my worksheet to the new 1.0.2. Followed the procedure to the point and i am receiving this error:

*



The query did not run or the Data Model could not be accessed. Here is the error message we got:

MdXScript(Model) (8, 4) Calculation error in measure 'Report'[Dividend %]: An argument of function 'LN' has the wrong data type or the result is too large or too small.

Click to expand...

*


----------



## larry81

Anyone had success with the new version ?


----------



## buddyb

*PS v 1.0.2*



larry81 said:


> Anyone had success with the new version ?


I just built up a small PortfolioSlicer v1.0.2 (Excel 2010) from scratch. Everything went in OK. Didn't see any issues. At first glance, I like what this latest version brings to the table.

I'll be transferring my real PS V 0.9.0 dataset over after a few Tax deadlines have passed. I've been messing with this program a number of months now, and I know this is the package I'm going to run long term with.


----------



## StockTrader

vidm said:


> Portfolio Slicer allows to calculate "Internal Rate of Return" - that is same as Excels XIRR function. In Portfolio Slicer it is called "RoR Symbol" and "RoR Account". (RoR stands for Rate of Return). But this formula works just for single symbol or for accounts (1 or more). It does not work on group of symbols - for example if you selected symbols in "Real Estate" allocation. Also, other % calculations (Profit%, Cap Gain %) is calculated monthly and then geometrically linked so money flows have less impact on results.


Thank you! Will have a look at it.


----------



## vidm

larry81 said:


> Just tried migrating my worksheet to the new 1.0.2. Followed the procedure to the point and i am receiving this error:


Hi Larry81,

For some reason I am not always getting notifications about new posts in this thread, sorry about my late reply. 
Release v1.0.2 is out and I am still working on documentation.

Suggestion for your issue would be:
1. Go to src tab into cell $AZ$3 - make sure selection in that cell is 2 or 0 (if 2 is not in drop down list). 
2. Go to src tab into cell $BF$3 - make sure selection in that cell is 3 or 0 (if 3 is not in drop down list). Try to refresh data. Documentation about this will be available soon. 
3. Can you please try suggestion 8 in this guide: http://portfolioslicer.com/Support/Data-Refresh-Issues. Your issue is very likely related to your transaction data. 

If that does not work, is there any chance you can send your workbook (http://portfolioslicer.com/Contact-Us)? Before sending for security reasons you can change numbers in workbook, just make sure that you still are getting same error message.


----------



## larry81

For an unknown reason, no more error today ! I just refreshed my 1.02 workbooks, everything is fine


----------



## vidm

larry81 said:


> For an unknown reason, no more error today ! I just refreshed my 1.02 workbooks, everything is fine


If you will encounter problem in the future, please make a copy of your workbook, so we can do investigation further. I know how frustrating is when you have these errors and I want to make sure that I handle them properly.


----------



## GGO

Vidm: I tried to update my spreadsheet latest with latest price but for some reason it seems always stuck with a price on previous date. Same procedure which works well before no longer update. Wonder what may go wrong. I am still at version 0.9. Thanks.


----------



## vidm

GGO said:


> Vidm: I tried to update my spreadsheet latest with latest price but for some reason it seems always stuck with a price on previous date. Same procedure which works well before no longer update. Wonder what may go wrong. I am still at version 0.9. Thanks.


Quotes availability does not depend on PS version. Can yo please post symbol example that was not available when you tried to update and what time you did update.
FYI - Stocks/ETFs quotes are updated few times a day. Mutual fund update is usually done after 9pm.


----------



## GGO

vidm said:


> Quotes availability does not depend on PS version. Can yo please post symbol example that was not available when you tried to update and what time you did update.
> FYI - Stocks/ETFs quotes are updated few times a day. Mutual fund update is usually done after 9pm.


Thanks vidm. When I go to powerpivot window and do refresh all, it does get updated and see green check mark. but when i go back to the holding tab, stock price is still the old price. I tried right click and update but nothing changes. On title page it does show latest date.


----------



## vidm

GGO said:


> Thanks vidm. When I go to powerpivot window and do refresh all, it does get updated and see green check mark. but when i go back to the holding tab, stock price is still the old price. I tried right click and update but nothing changes. On title page it does show latest date.


If you go to Holdings tab - please check slicers "Year" and "Month". If you selected any value in these slicers, then you will see holdings report for the end of period you selected. So if you selected Month "4", you will see holdings as if they were at the end of April 2015. Maybe that is your issue?


----------



## GGO

Bingo, you nailed it. Thanks so much.


----------



## GreatLaker

Has anyone else noticed that quotes are not updating today? The holdings tab shows "Last Quote Update 2015-05-14 16:22:59 ET.
But the prices in PS are from yesterday. Examples: XEF 27.24, VCN 30.22, HXT 28.06.
When I look in the Quotes tab of the PowerPivot window it does not show any quotes newer than 2015-05-13.

I updated to version 1.0.2 today; not sure if that would have any effect. PowerPivot does seem to update correctly. It indicates Success and one of the items says Quotes Success 2,246 Rows Transferred


----------



## vidm

GreatLaker said:


> Has anyone else noticed that quotes are not updating today? The holdings tab shows "Last Quote Update 2015-05-14 16:22:59 ET.
> But the prices in PS are from yesterday. Examples: XEF 27.24, VCN 30.22, HXT 28.06.
> When I look in the Quotes tab of the PowerPivot window it does not show any quotes newer than 2015-05-13.
> 
> I updated to version 1.0.2 today; not sure if that would have any effect. PowerPivot does seem to update correctly. It indicates Success and one of the items says Quotes Success 2,246 Rows Transferred


Sorry, there was a problem with quotes that I just resolved. Please refresh your data now - you should see todays quotes.


----------



## GreatLaker

Yes it is working now. Thanks very much VDIM.
Nice update. it is much faster to refresh the pivot tables.

One thing I noticed in the new release is on the Monthly and Yearly tabs the charts comparing to the indexes, the values for Index2 % are all at 100.00%. I attached a screen capture.

Thanks again.


----------



## vidm

GreatLaker said:


> Yes it is working now. Thanks very much VDIM.
> Nice update. it is much faster to refresh the pivot tables.
> 
> One thing I noticed in the new release is on the Monthly and Yearly tabs the charts comparing to the indexes, the values for Index2 % are all at 100.00%. I attached a screen capture.
> 
> Thanks again.
> 
> View attachment 4465


Could you please go to your web workbook (web interface, "edit" next to your workbook) and check what is your "Market Index2" symbol. Did you change that symbol from original value? Can you use symbol ^GSPC and see if that works better?

As you notice this release has lots of improvements for performance - I had to spend lots of time on that. I have users with many thousands of transactions who complained, so I did my best...


----------



## GreatLaker

Market Index 2 is "^GSPC". And Market Index 1 is "^GSPTSE".
I didn't edit the values.


----------



## vidm

GreatLaker said:


> Market Index 2 is "^GSPC". And Market Index 1 is "^GSPTSE".
> I didn't edit the values.


I checked behavior on all my versions, and I do not see this not in Excel 2010/2013. There is something else affecting Market Index 2 result. Is there any way you can delete all your transactions (or leave few, change numbers) then refresh (and confirm that issue is still there) and send me (http://portfolioslicer.com/Contact-Us) your workbook?


----------



## GreatLaker

I sent it to you by email.
Thanks


----------



## vidm

GreatLaker said:


> I sent it to you by email.
> Thanks


Thank you! I identified issue - all you symbols are CAD and because of that I do not bring in currency conversion data. Because "^GSPC" is in USD and I do not have USD conversion data, you get 100% value.
Solutions:
1. Change your Market Index 2 to something in CAD currency. You have to find what fits you best. Pros: smaller data set, faster refresh. Cons: hard to find proper index that might fit you (inception date, MER does not drag down too much, etc)
2. To your web workbook add at least one symbol with USD currency (any, example MSFT). Pros: You can look at your portfolio in CAD and USD. Cons: slightly slower because of more data and conversion.

Let me know if you still have issue after applying one of the above listed suggestions.


----------



## GreatLaker

Solution 2 fixed it.
Thanks again.


----------



## m3s

I have finally migrated to the new version! I like the refined appearance and additions. I no longer have to put in a password to update the prices!? I was also able to download a pre setup workbook with my code already embedded, and then it was simply a matter of a few copy/paste to get on the latest and greatest.

Everything on the Dashboard is just how it should be. I especially like seeing Dividends YTD vs Last YTD and the exchange rate impact. Is there a way to override the sector pie chart like the allocation one though? My sector chart isn't very accurate to me (I'm guessing it defines a holdings sector from the web somewhere?)



vidm said:


> So if your issue is with [Profit %], I probably can explain why in certain situations you see results that are not as expected. But I would have to see workbook with that symbol.
> If you have [Profit] value not expected, then there is something wrong in my formulas and I would have to investigate...
> There is big fix coming in next release for ACB calculation. There is no issue if you do series of buys and then one sale. But as you start to have multiple sales on the same symbol, current (0.9 release) book value is average over ALL time and next version will have proper recursive calculation for that.


My issue was with the a few of the [YTD Profit %] which I only saw on the Dashboard "Top 10 YTD Winner/Loser" charts. The ones I was confused about have now disappeared from the Top 10's. They did have multiple buy/sell, so it could be the updated ACB calc or profit %. Keep up the good work :encouragement:


----------



## GreatLaker

I input "Used From Date" and "Used Up To Date" on my Web Workbook for symbols that I have not held for the entire timeframe of my transactions in PS, and found that the PowerPivot update is a lot faster. Another great feature addition.


----------



## vidm

m3s said:


> I have finally migrated to the new version! I like the refined appearance and additions. I no longer have to put in a password to update the prices!? I was also able to download a pre setup workbook with my code already embedded, and then it was simply a matter of a few copy/paste to get on the latest and greatest.
> 
> Everything on the Dashboard is just how it should be. I especially like seeing Dividends YTD vs Last YTD and the exchange rate impact. Is there a way to override the sector pie chart like the allocation one though? My sector chart isn't very accurate to me (I'm guessing it defines a holdings sector from the web somewhere?)
> 
> 
> 
> My issue was with the a few of the [YTD Profit %] which I only saw on the Dashboard "Top 10 YTD Winner/Loser" charts. The ones I was confused about have now disappeared from the Top 10's. They did have multiple buy/sell, so it could be the updated ACB calc or profit %. Keep up the good work :encouragement:


m3s - I receive sector information from my source provider as "bonus" and I know up front that data is not very accurate. There is no way at this point for end user to override sector allocation, but if you would post here or send me private symbol, sector, sector percent distribution, I will update them. If you do not have sector distributions, send me just list of symbols and I'll review them for accuracy, but that will take longer.

I am glad you like new release. Yes, with Excel 2013 you do not need to enter password anymore and I generate workbook with embedded code for each user - this makes it more user friendly. 
ACB calculation is now properly calculated with multiple buy/sell transactions. I used new ACB calculations for my own cap gain calculations for tax filing as last year I had a lot of sales (I am simplifying my portfolio and going with "couch potato" strategy). All numbers for taxes matched exactly between my manual calculations and PS. I hope this ACB feature will be useful for all PS users.

Microsoft already announced list of new functions that will be available in next Excel 2016 release. XIRR is one of the functions that I am really excited about - so all performance measurement in the future PS versions for Excel 2016+ will be much easier to calculate. There are many other features I want to add to PS, I just need time


----------



## vidm

GreatLaker said:


> I input "Used From Date" and "Used Up To Date" on my Web Workbook for symbols that I have not held for the entire timeframe of my transactions in PS, and found that the PowerPivot update is a lot faster. Another great feature addition.


Thanks! Yes, I added these fields exactly for that - to reduce number of quotes downloaded - and that speeds up download time and then pivot refresh time. Originally I had report that showed these values (from/to dates) for each symbol, but then I removed that report and calculations to avoid confusion. I decided that advanced users like you will figure out how to use it. But I will at some point write "PS performance tuning" tips.


----------



## vidm

larry81 said:


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


Larry81 - this is now done and major currency exchange rates are updated few times per day. Thanks for this great suggestion!


----------



## larry81

I just want to say how happy i am tracking my portfolio with portfolioslicer spreadsheets


----------



## vidm

larry81 said:


> I just want to say how happy i am tracking my portfolio with portfolioslicer spreadsheets


Just a warning - if you are using Excel 2010 please wait with update to Windows 10. At this point you cannot use Excel 2010 with PowerPivot on Windows 10 as there are known issues (PowerPivot will crash) - hopefully Microsoft will provide a fix soon. Because of that you cannot use Portfolio Slicer with Excel 2010 on Windows 10.


----------



## larry81

vidm said:


> Just a warning - if you are using Excel 2010 please wait with update to Windows 10. At this point you cannot use Excel 2010 with PowerPivot on Windows 10 as there are known issues (PowerPivot will crash) - hopefully Microsoft will provide a fix soon. Because of that you cannot use Portfolio Slicer with Excel 2010 on Windows 10.


Happy Win7 and Office2013 user here 

The only recent bug i observed is that the USD/CAD range for 2015-07-31 is not present, it just straight from 2015-07-30 to 2015-08-03.


----------



## vidm

There is an issue with July 31st exchange rate data - I believe this will be fixed tomorrow.


----------



## GreatLaker

*New verion of PortfolioSlicer*

I just noticed a new version of PortfolioSlicer! I don't have time to ugrade now, but posting in case anyone wants to have a look at it.

http://www.portfolioslicer.com/news.html


> 2016-03-18 Portfolio Slicer version 2.0.0 Released
> 
> This release removes Portfolio Slicer dependency on cloud data. From this point Portfolio Slicer becomes Excel workbook that uses data from within workbook and also some data from external files from your PC. Portfolio Slicer does not provide data for these external data files. These files can be created by multiple data services (if you have subscription) or there are already free scripts published by Portfolio Slicer users that will get data from websites in the format that is required by Portfolio Slicer.
> 
> This release also includes few minor fixes in data model calculations.



http://www.portfolioslicer.com/mydata.html


> Older version of Portfolio Slicer required data about your investments to be entered into web workbook. From this page you can still access old website of Portfolio Slicer where you can still work with your old version of web workbook.
> 
> To access your old version of web workbook please visit here.
> 
> Please note that old version of Portfolio Slicer will be supported until June 30th, 2016. After that any access to web workbook or any other online/cloud data will be removed. Please migrate to new version of Portfolio Slicer before this deadline.


----------



## vidm

Just a clarification - there are already scripts released by other Portfolio Slicer users that will get all data you need to run Portfolio Slicer. 
Upgrade process from older version should be fairly simple - you will find most of the data and even configuration file provided to you in extract. There is video available on how to migrate: http://www.portfolioslicer.com/docs/upgrade.html
I had many requests to remove PS dependency on cloud data. I guess people were afraid that after they will put effort in setting up Portfolio Slicer, they might get hit with monthly/yearly fees. Now you just get workbook and scripts and use it any way you like - no limitations.


----------



## GreatLaker

Hi Vdim,
You previously noted a problem using PortfolioSlicer with Excel 2010 and Windows 10:
http://canadianmoneyforum.com/showt...folio-tracker)?p=767754&viewfull=1#post767754

Is that still a problem with PortfolioSlicer version 2? I am currently using Windows 7 with Excel 2010 (32 bit), but want to upgrade to Win10..

Thank you.


----------



## larry81

Hi vidm, great news regarding version 2.0 !

Quick question for you, i used to own a few mutual fund a year back. Since they were sold many years ago and that i dont hold them anymore. Do i need to add them as source in the new v2.00 ? 

edit: i am having trouble following the ~20 steps migration path, receiving error:

We couldn't get data from the Data Model. Here's the error message we got:

The AttributeRelationship with AttributeID =- Symbol doesnt exist in the collection


----------



## vidm

GreatLaker said:


> Hi Vdim,
> You previously noted a problem using PortfolioSlicer with Excel 2010 and Windows 10:
> http://canadianmoneyforum.com/showt...folio-tracker)?p=767754&viewfull=1#post767754
> 
> Is that still a problem with PortfolioSlicer version 2? I am currently using Windows 7 with Excel 2010 (32 bit), but want to upgrade to Win10..
> 
> Thank you.


Windows 10 is not a problem - I am using WIndows 10 with Excel 2010.


----------



## vidm

larry81 said:


> Hi vidm, great news regarding version 2.0 !
> 
> Quick question for you, i used to own a few mutual fund a year back. Since they were sold many years ago and that i dont hold them anymore. Do i need to add them as source in the new v2.00 ?
> 
> edit: i am having trouble following the ~20 steps migration path, receiving error:
> 
> We couldn't get data from the Data Model. Here's the error message we got:
> 
> The AttributeRelationship with AttributeID =- Symbol doesnt exist in the collection


Old mutual funds - you do not need to add them into configuration file, but you should list them in symbol table and for proper history you need to have quotes for them. These mutual funds quotes file should be in "Quotes" folder in any subfolder, for example I created myself "Manual" subfolder were I add quotes for these symbols that are not traded anymore.

For problem you have:
1. Make sure that Trans table has no "Red" cells, specifically check Symbol name - it sounds to me that you have some missing symbols, but I am not 100%.
2. Leave just 2-3 transactions in Trans table, delete all other records and try to refresh


----------



## larry81

vidm said:


> Old mutual funds - you do not need to add them into configuration file, but you should list them in symbol table and for proper history you need to have quotes for them. These mutual funds quotes file should be in "Quotes" folder in any subfolder, for example I created myself "Manual" subfolder were I add quotes for these symbols that are not traded anymore.
> 
> For problem you have:
> 1. Make sure that Trans table has no "Red" cells, specifically check Symbol name - it sounds to me that you have some missing symbols, but I am not 100%.
> 2. Leave just 2-3 transactions in Trans table, delete all other records and try to refresh


I just re-verified, there no red anywhere.
I also deleted all transaction except 2-3

same error


----------



## vidm

larry81 said:


> I just re-verified, there no red anywhere.
> I also deleted all transaction except 2-3
> 
> same error


Based on other people experience reported so far, most likely reason for failure:

1. Make sure that first record in table Symbol with "* Cash" was not replaced/deleted. 

If that is not the case, then other things to check - please very carefully review table Symbol and SymbolSector:

2. Symbol[Currency] (table Symbol, column Currency) should have values in report currency list or "Cash".
3. Symbol[MER] values should be decimal number and not character. Make column wider and confirm that all values align left.
4. Symbol[WHTPercent] - put all values (except first row) as 0.
5. SymbolSector[Percent] - make sure all values are numbers.

Let me know if you still have any issues.


----------



## larry81

PVI folks, new version of portfolioslicer (v2.1) and a brand new version with PowerBI support !

http://portfolioslicer.com/download.html

Big thumbs up for our very own vidm !


----------



## newuser

larry81 said:


> PVI folks, new version of portfolioslicer (v2.1) and a brand new version with PowerBI support !
> 
> http://portfolioslicer.com/download.html
> 
> Big thumbs up for our very own vidm !


That PowerBI stuff looks awesome! Can't wait to try this out!


----------



## m3s

Paging @vidm

I heard that Yahoo Finance API was recently shut down? Does this affect the latest version? The only thing I use Office for anymore is PortfolioSlicer and I don't really want to buy it for my new PC if PortfolioSlicer won't work..



> Download v2.3.1 (2017-Sep-18): External data _Download and Management script set__ (for majority of users). Latest update brings back support for extracting Quotes/Dividends from Yahoo Finance website and adds ability to get currency exchange rates from European Central Bank. Also added new source for quotes: AlphaVintage and Stooq._


----------



## vidm

m3s said:


> Paging @vidm
> 
> I heard that Yahoo Finance API was recently shut down? Does this affect the latest version? The only thing I use Office for anymore is PortfolioSlicer and I don't really want to buy it for my new PC if PortfolioSlicer won't work..


- Yahoo Finance API for Historical Data was changed (not shut down) few months ago, but updated PS Scripts (available for download now) work with updated versions.
- Yahoo Finance API for IntraDay data was shut down very recently, but updated scripts that support intraday quote extract using different method should be available on Portfolio Website very soon
- On PortfolioSlicer website there scripts now available that allow you to get quotes from Stooq and AlphaVantage websites
- Currency exchange rates are now available from Stooq and European Central Bank.

So PortfolioSlicer works with even more options for quotes and currency exchange data.


----------



## larry81

Hey vidm, when people justify their Microsoft Office license because they use Portfolioslicer, you know you did a good job. Keep up the good work on buddy !


----------



## OnlyMyOpinion

vidm said:


> ...To enter all these transactions is not an easy task....


Not true IMO. 
I have no idea what a person would do with all the 'fluff' contained in portfolio slicer. Do all the tables, graphs, pie charts, coloured highlights really lead to better portfolio management and decisions? ISTM if you have a simple and/or long term portfolio you don't need it, and if you are an active trader it doesn't cut it either. To each their own however.


----------



## m3s

vidm provides this for free by the way

Yes I find asset allocation, currency impact and dividend stats useful to benchmark a long term portfolio of individual holdings. The portfolioslicer reports seem more accurate and intuitive to me than Quicken or any brokerage dashboard I've tried. Brokerage reports are so inaccurate I'm surprised everyone just shrugs it off like typical Canadians always do. Quicken is a lost cause.

Sure you can make your own but this one has been fine tuned already


----------



## jdam

i didn't check all the pages here. but not sure if anyone use: RCH_Stock_Market_Functions
it can pull stock quotes and details from yahoo into excel automatically. it still works for me as of march 25 2018. it is very useful.
url is:
groups.yahoo.com/neo/groups/smf_addin/info


----------



## larry81

FYI friends, new version of portfolioslicer is out ! (2.4 beta):
http://www.portfolioslicer.com/download.html


----------



## larry81

Anyone managed to get the TD e-series fund in portfolioslicer now that Google Finance deprecated the quotes services...?


----------

