Personal Finance

Investment tracking with Excel

  • Last Updated:
  • Mar 22nd, 2014 2:05 pm
[OP]
Jr. Member
Jan 13, 2014
130 posts
24 upvotes
Toronto, ON

Investment tracking with Excel

Can somebody point to software they use to track investments. I am good at Excel, so I would very much prefer something in Excel environment, but I would like to reports to be much more just a current snapshot information.
I would also consider other software outside of Excel, but primary requirement is that any information I enter in, I should be able easily to get out. I would consider paying for such software, but do not like yearly/monthly subscription model.

TIA!
15 replies
Sr. Member
Nov 28, 2010
979 posts
176 upvotes
Toronto
google finance
[OP]
Jr. Member
Jan 13, 2014
130 posts
24 upvotes
Toronto, ON
yp_1 wrote: google finance
It appears to me that Google finance Portfolio (or Yahoo, or MSN) does not track dividends paid or currency exchange rates fluctuation impact.
Sr. Member
User avatar
Mar 13, 2012
863 posts
142 upvotes
Planet Earth
If you want full manual mode and track distributions yourself then smf_addIn add-on for Excel will pull the daily information from Yahoo Finance and you Roll-Your-Own from scratch using array formulas and vlookups. I use a custom generated investment report from Quicken and paste to Excel as my source input data and maintain daily updates via smf_addin.

If you just want a portfolio tracker that will call out dividend payments then a portfolio at Morningstar is the only one I know which will notify you about distributions/dividends.
If at first you don't succeed, destroy all evidence that you even tried.
[OP]
Jr. Member
Jan 13, 2014
130 posts
24 upvotes
Toronto, ON
Allen32 wrote: If you want full manual mode and track distributions yourself then smf_addIn add-on for Excel will pull the daily information from Yahoo Finance and you Roll-Your-Own from scratch using array formulas and vlookups. I use a custom generated investment report from Quicken and paste to Excel as my source input data and maintain daily updates via smf_addin.

If you just want a portfolio tracker that will call out dividend payments then a portfolio at Morningstar is the only one I know which will notify you about distributions/dividends.
Thank you very much for links and suggestions! I'll be looking and Portfolio Slicer (reports look great) and smf_addIn. If anyone has experience with these products I would appreciate if you share it.
Sr. Member
User avatar
Mar 13, 2012
863 posts
142 upvotes
Planet Earth
Portfolioslicer appears to have a problem with Excel 2013 right now. Demo seems to work but personal portfolio is broken. Perhaps I will revisit later when it's fixed as it looks pretty slick. It would be brutal to transfer all the info over from my current data but that can probably be figured out easily enough.
If at first you don't succeed, destroy all evidence that you even tried.
Member
Sep 24, 2012
347 posts
30 upvotes
MISSISSAUGA
Actually I downloaded Acemoney lite(free) and noticed it also downloads dividends.
Sr. Member
Nov 28, 2010
979 posts
176 upvotes
Toronto
For some reason Morningstar does not convert USD/CAD when it comes to cash tracking of USD stock. The amounts get withdrawn from cash in USD without conversion to CAD.
And I was not able to find a setting to change it :(
The rest looks good. Especially I liked the X-Ray feature.
Sr. Member
User avatar
Mar 13, 2012
863 posts
142 upvotes
Planet Earth
yp_1 wrote: For some reason Morningstar does not convert USD/CAD when it comes to cash tracking of USD stock. The amounts get withdrawn from cash in USD without conversion to CAD.
And I was not able to find a setting to change it :(
Ya you can't do that... I had the same problem and ended up creating a USD base currency portfolio. I think it actually worked out better in the long run becuase it reports more accurately.

From the FAQ:
When I add stocks or funds that are a combination of CAD and U.S. dollars what currency is my portfolio?

When adding stocks or fund holdings the currency type automatically defaults to the currency of that holding. It can be CAD or U.S., no matter what the currency type is of the holding the portfolio will always be what the base currency is that you chose when creating your portfolio. If the base currency is CAD and your portfolio contains CAD holdings and U.S. holdings the overall portfolio will be reported in Canadian dollars
If at first you don't succeed, destroy all evidence that you even tried.
Sr. Member
Nov 28, 2010
979 posts
176 upvotes
Toronto
Allen32 wrote: Ya you can't do that... I had the same problem and ended up creating a USD base currency portfolio. I think it actually worked out better in the long run becuase it reports more accurately.

From the FAQ:
Too bad, but I guess I can live with it.
At least the total line of the portfolio reports proper amount (ie. it converts USD stock to CAD before adding to the bottom line).

P.S. Just noticed there is 15% discount when you subscribe. Emailed support if it can be applied to my account after the 14-day trial is over.
Here is the link
Member
Dec 8, 2006
369 posts
24 upvotes
I just use internal functions within google spreadsheets. Here is an article I wrote about creating a stock watchlist with google spreadsheets:
http://www.milliondollarjourney.com/how ... sheets.htm
Million Dollar Journey - Since hitting the $1M milestone, we have increased our focus on passive income. Come follow our journey to financial freedom and possibly early retirement.
Sr. Member
User avatar
Mar 13, 2012
863 posts
142 upvotes
Planet Earth
Anonymouse wrote: I tried to get Porfolioslicer to work with Excel 2013, and gave up after an hour and a half. Not even the demo would run for me. I will try again in a couple of weeks because it has a lot of promise.
The guy has updated the sheet. I haven't downloaded it to check it as I created my one PowerPivot based and slicers.
http://portfolioslicer.com/News/2014-Ne ... 1-Released
If at first you don't succeed, destroy all evidence that you even tried.
[OP]
Jr. Member
Jan 13, 2014
130 posts
24 upvotes
Toronto, ON
I am using Portfolio Slicer (Excel 2010) for my investments. Thanks again MehtabSingh for your suggestion! Entering data was most difficult task - I had to enter all transaction data and for now I added just last 3 years of data. Scotia bank has ability to extract .csv files for transactions, for TD I copied transactions from PDFs. Then I transformed data into format required and loaded into Portfolio Slicer.
Overall I am happy that now I have my investment transaction database with powerful reporting - that was my ultimate goal.
Deal Addict
User avatar
Apr 16, 2009
1355 posts
597 upvotes
Vancouver
Anyone else having issues with the updated Portfolio Slicer?
I created an online workbook with all the TD e-series funds I plan to use.
I put the code into excel for it to update, but it gives me and AttributeID = Trans ID error...

I posted on his forum so hopefully I'll get that figured out, just wanted to see if any of you had the same issue.

[EDIT]
Got a reply http://portfolioslicer.com/Forum/g/post ... 3#post2093

He's releasing an updated version tonight for a bug he found .

Top