[vidm]

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]

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]

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]

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]

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]

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]

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]

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]

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

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]

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]

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]

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]

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.