Canadian Money Forum banner
1 - 11 of 11 Posts

·
Registered
Joined
·
5,627 Posts
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!
 

·
Registered
Joined
·
5,627 Posts
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?
 

·
Registered
Joined
·
5,627 Posts
Ahh, so aliases could have been useful. What I did is "replace all" instead

I need clarification regarding "make the stock quantity for a sale an absolute value (positive or negative)". When you enter transactions, for sales you already enter absolute value. In my reports sales reports also quantities and amounts are absolute. Can you please clarify this change - which report?
My data had all the sell transactions with a negative quantity of stocks. This threw my reports out of whack on portfolio slicer but I quickly found that and removed the negative signs. However, a sale is always intended to be a sale (it's never a buy as you would use buy transaction) So I mean you could accept + or - stock quantity for sell

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

·
Registered
Joined
·
5,627 Posts
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:
 

·
Registered
Joined
·
5,627 Posts
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.
 

·
Registered
Joined
·
5,627 Posts
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!
 

·
Registered
Joined
·
5,627 Posts
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 :)
 

·
Registered
Joined
·
5,627 Posts
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?
 

·
Registered
Joined
·
5,627 Posts
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?)

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:
 

·
Registered
Joined
·
5,627 Posts
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.
 

·
Registered
Joined
·
5,627 Posts
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
 
1 - 11 of 11 Posts
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top