Portfolioslicer (Automated Excel portfolio tracker) - Page 10
Page 10 of 16 FirstFirst ... 89101112 ... LastLast
Results 91 to 100 of 152

Thread: Portfolioslicer (Automated Excel portfolio tracker)

  1. #91
    Member
    Join Date
    Mar 2012
    Posts
    85
    Quote Originally Posted by mf4361 View Post
    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.

    Free investment tracking in Excel: Hidden Content

  2. #92
    Member
    Join Date
    Mar 2012
    Posts
    85
    Quote Originally Posted by GGO View Post
    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.
    Free investment tracking in Excel: Hidden Content

  3. #93
    Member
    Join Date
    Feb 2015
    Posts
    35
    Quote Originally Posted by vidm View Post
    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.

  4. Remove Advertisements
    CanadianMoneyForum.com
    Advertisements
     

  5. #94
    Senior Member
    Join Date
    Apr 2015
    Posts
    133
    Quote Originally Posted by vidm View Post
    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.

  6. #95
    Senior Member m3s's Avatar
    Join Date
    Apr 2010
    Location
    Everywhere
    Posts
    3,112
    Quote Originally Posted by vidm View Post
    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

    Quote Originally Posted by vidm View Post
    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.
    amat victoria curam

  7. #96
    Member
    Join Date
    Mar 2012
    Posts
    85
    Quote Originally Posted by m3s View Post
    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.
    Free investment tracking in Excel: Hidden Content

  8. #97
    Senior Member m3s's Avatar
    Join Date
    Apr 2010
    Location
    Everywhere
    Posts
    3,112
    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!
    amat victoria curam

  9. #98
    Member
    Join Date
    Mar 2012
    Posts
    85
    Quote Originally Posted by m3s View Post
    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.
    Free investment tracking in Excel: Hidden Content

  10. #99
    Senior Member m3s's Avatar
    Join Date
    Apr 2010
    Location
    Everywhere
    Posts
    3,112
    Quote Originally Posted by vidm View Post
    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 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
    amat victoria curam

  11. #100
    Member
    Join Date
    Mar 2012
    Posts
    85
    Quote Originally Posted by m3s View Post
    OK so I don't plan to write DAX anytime soon but I see kind of what you were doing 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.

    Free investment tracking in Excel: Hidden Content

Page 10 of 16 FirstFirst ... 89101112 ... LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •