Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Best way to track portfolio performance

Discussions regarding financial software
BitterLemon
Posts: 22
Joined: March 19th, 2020, 1:33 pm
Has thanked: 3 times
Been thanked: 1 time

Best way to track portfolio performance

#292357

Postby BitterLemon » March 19th, 2020, 1:56 pm

Hello Lemons!
New poster so please be gentle.

I have a question on something which I've been looking for a good solution to for a long to no avail. To be honest I'm totally overwhelmed by it.

I'm looking for a tool or way to track my portfolio performance over time. I understand that knowing what performance your portfolio has achieved over time is a key component of good investing. Specifically I made a decision to invest rather than pay off the mortgage many years ago and would like to review the outcome so far to inform my next move. Over this period I've acquired numerous accounts and moved investment platform a several times so don't have a holistic view nor accurate history.

Most of the platforms i've used tell you whether you are up or down against your investment but rarely give you an annualized return figure. I've looked at generic web portfolio account tools on morning star, ft and trustnet which aren't bad but don't support some of the things that are really fundamental. eg with trustnet you can record buying £1000 of a stock but if you sell £500 of it some time later, there's no way to record that.

I'd really like something which can handle this and be . Most of the above require keying everything into the websites and so are rather painful.

Ive spent a lot of time recently trying to get these 2 spreadsheets working with my data
-Investment moat spreadsheet docs.google.com/spreadsheets/d/1fLoNjGCqyjytiaEdolGRvwCjgyQ6dJKVEGeGaQ_rjok/edit#gid=2107485586
-Stock Portfolio Tracking Spreadsheet docs.google.com/spreadsheets/d/1Ajox_mGj_prTqfIWSSF1xcaDEBm6ZuZZDInZ1Es-_bM/edit#gid=4
but there are lots of issues (not least the data consolidation and the lack of a consistent and historic pricing data feed) and I'm wishing there is a better way!


Wish list:
-Long term (ie 15 years +) stability
-"x-ray" views on the whole portfolio
-support multi-platform with ability to break down by account
-easy to import data into
-copes with multiple trades of same stock
-can give valuations at user defined points in time, annualized returns etc


I wonder if you know of any tools be they websites, apps, spreadsheets that do this well?

Otherwise i'll be spending another 100-200 hours of death-by-excel trying to make one of these spreadsheet work and I fear that even if i manage to it will be riddled with shortcomings

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: Best way to track portfolio performance

#292404

Postby kiloran » March 19th, 2020, 3:27 pm

I'm sure there is no "Best Way". Everyone has different needs or preferences and I suspect most people create their own tools.

StepOne created a portfolio tool many years ago....... http://lemonfoolfinancialsoftware.weebl ... folio.html

If you want to get Yahoo prices into an Excel or LibreOffice portfolio, this may be of interest..... http://lemonfoolfinancialsoftware.weebl ... crape.html
or this.... http://lemonfoolfinancialsoftware.weebl ... nload.html
or this.... http://lemonfoolfinancialsoftware.weebl ... excel.html

If you want to get Google prices into an Excel or LibreOffice portfolio, this may be of interest.....http://lemonfoolfinancialsoftware.weebl ... price.html

Hopefully this gives you some food for thought

--kiloran

BitterLemon
Posts: 22
Joined: March 19th, 2020, 1:33 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Best way to track portfolio performance

#292423

Postby BitterLemon » March 19th, 2020, 4:10 pm

Thanks Kiloran.

I take the point on "best" - I suppose i meant for my requirements.

StepOne's spreasheet is a little simplistic for my needs. The 2 spreadsheets i referenced are more capable than this though even then there are a few things they don't do.

Yes I've read loads on the whole history of getting google finance quotes, yahoo, morningstar, blloomberg etc

All of the fragility of screen scraping and the need to keep fixing it as providers change their websites is offputting.

I don't mind putting some time into building something now but I just fear I'll be too busy to maintain it in the future when it breaks.

Since the demise of google finance for UK users none of these feeds seem to offer historic performance. I did find a google spreadsheet which automates nightly pulls from yahoo and collates the data but that's obviously no good for history before you build the spreadsheet.


I suppose I was hoping there was a non-spreadsheet soluion like a great whizzy app/ website which makes a lot of this pain go away.

tjh290633
Lemon Half
Posts: 8208
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4096 times

Re: Best way to track portfolio performance

#292437

Postby tjh290633 » March 19th, 2020, 4:47 pm

In my view the simplest way is to construct a cash flow table for your portfolio, based on cash in and cash out, ignoring cash arising inside the portfolio which stays in the portfolio. Dates in the first column, cash movements in the second column, positive for inflow and negative for outflow (or the current value) and apply Excel's XIRR formula to the table. This will give you a measure of the total return.

It's not hard to do, if you have the data available, and will be as good as anything that you will find elsewhere.

TJH

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: Best way to track portfolio performance

#292439

Postby kiloran » March 19th, 2020, 4:49 pm

BitterLemon wrote:Since the demise of google finance for UK users none of these feeds seem to offer historic performance. I did find a google spreadsheet which automates nightly pulls from yahoo and collates the data but that's obviously no good for history before you build the spreadsheet.

The GoogleFinance function in Google Docs does provide historic prices: https://support.google.com/docs/answer/3093281?hl=en-GB
You could use this along with the technique described at http://lemonfoolfinancialsoftware.weebl ... price.html to get the historic prices into Excel or LibreOffice.

One advantage of building your own solution is that you will understand exactly what it does.

--kiloran

BitterLemon
Posts: 22
Joined: March 19th, 2020, 1:33 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Best way to track portfolio performance

#292465

Postby BitterLemon » March 19th, 2020, 5:59 pm

kiloran wrote:
BitterLemon wrote:Since the demise of google finance for UK users none of these feeds seem to offer historic performance. I did find a google spreadsheet which automates nightly pulls from yahoo and collates the data but that's obviously no good for history before you build the spreadsheet.

The GoogleFinance function in Google Docs does provide historic prices:
You could use this along with the technique described at to get the historic prices into Excel or LibreOffice.

One advantage of building your own solution is that you will understand exactly what it does.

--kiloran


It's hard to know for certain if you havent been following it but theres lots of online commnets saying its no longer supported. Being undocumented doesnt help!

Anyway this works fine:
=GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")

But if i replace the tickers with the following i get ...'FUND_EQUI_T_1UJO18H' returned no data.

LON:FUND_EQUI_T_1UJO18H
LON:IP_INCO_ACC_475LZL
LON:VANG_FTSE_UK_CUI42L
LON:VANG_US_EQUI_1NU5XTO
LON:BLAC_UK_SPEC_T5Y28K

Maybe it's a funds vs shares thing?
Last edited by BitterLemon on March 19th, 2020, 6:13 pm, edited 1 time in total.

BitterLemon
Posts: 22
Joined: March 19th, 2020, 1:33 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Best way to track portfolio performance

#292467

Postby BitterLemon » March 19th, 2020, 6:11 pm

tjh290633 wrote:In my view the simplest way is to construct a cash flow table for your portfolio, based on cash in and cash out, ignoring cash arising inside the portfolio which stays in the portfolio. Dates in the first column, cash movements in the second column, positive for inflow and negative for outflow (or the current value) and apply Excel's XIRR formula to the table. This will give you a measure of the total return.

It's not hard to do, if you have the data available, and will be as good as anything that you will find elsewhere.

TJH


Yes I was considering something more straightforward vaguely along these lines but you've brought some method and clarity so thanks for that and the excel function - useful to know it exists.

I'm knee-deep in trying to get historical pricing which will allow more granular performance monitoring. It's a bit beyond my original intention but once you start investing time in these things your appetite grows! It's great to have your approach as a fall back though.

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 209 times

Re: Best way to track portfolio performance

#292547

Postby torata » March 20th, 2020, 4:10 am

Hello BL

I'm a massive fan of MS Money, the sunset version of which is free and available
https://www.microsoft.com/en-us/download/details.aspx?id=20738
It does all the things you are looking for.
(It does a lot more, but I only use the Investing section and Reports sections, plus the Banks section to record my NSI and some savings)

The only immediate disadvantage is that it doesn't automatically download share prices, but there are a couple of fixes available on line for that if you want. However I input manually twice a month, which a) leaves me in control of how often I want to get an overview, and b) works as a kind of zen-type activity bringing me closer to each instrument.
I can export the info from MS Money into spreadsheets to give me my unitisation and some other things I track like dividend increase over time.

I have also the portfolio set up on a google sheet to give me the up to date prices (for MS Money input) and an immediate snapshot of portfolio balance (and copes with the 3 currencies I use).

torata

BitterLemon
Posts: 22
Joined: March 19th, 2020, 1:33 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Best way to track portfolio performance

#292636

Postby BitterLemon » March 20th, 2020, 11:18 am

torata wrote:Hello BL

I'm a massive fan of MS Money, the sunset version of which is free and available

It does all the things you are looking for.
(It does a lot more, but I only use the Investing section and Reports sections, plus the Banks section to record my NSI and some savings)

The only immediate disadvantage is that it doesn't automatically download share prices, but there are a couple of fixes available on line for that if you want. However I input manually twice a month, which a) leaves me in control of how often I want to get an overview, and b) works as a kind of zen-type activity bringing me closer to each instrument.
I can export the info from MS Money into spreadsheets to give me my unitisation and some other things I track like dividend increase over time.

I have also the portfolio set up on a google sheet to give me the up to date prices (for MS Money input) and an immediate snapshot of portfolio balance (and copes with the 3 currencies I use).

torata

Hi Torata

Thanks for the suggestion. It sounds ideal.

I've just installed MS Money but it seems I need to key in each trade manually. I really wanted to be able to load a list of transactions - I have a lot as some of my providers do things like sell a tiny chunk of some holdings each month to pay fees and some income units get reinvested. I tried loading a csv file but it didn't seem to like it, only seems to want QIF or ofx formats. I just have data that ive copy pasted from my various platforms which I've started to standardise into one big csv. Am i missing something?

Degsy67
Lemon Pip
Posts: 96
Joined: November 4th, 2016, 7:32 pm
Has thanked: 76 times
Been thanked: 68 times

Re: Best way to track portfolio performance

#292801

Postby Degsy67 » March 20th, 2020, 11:37 pm

BitterLemon wrote:Hello Lemons!
New poster so please be gentle.

I have a question on something which I've been looking for a good solution to for a long to no avail. To be honest I'm totally overwhelmed by it.

I'm looking for a tool or way to track my portfolio performance over time. I understand that knowing what performance your portfolio has achieved over time is a key component of good investing. Specifically I made a decision to invest rather than pay off the mortgage many years ago and would like to review the outcome so far to inform my next move. Over this period I've acquired numerous accounts and moved investment platform a several times so don't have a holistic view nor accurate history...


Take a look at the following software. From memory it’s free to download the 30 day trial version before deciding on purchasing...

https://www.fundmanagersoftware.com/hel ... ation.html

I have no affiliation with the company and don’t use it myself so it warrants a little bit of personal context from me...

A few years ago I was running a complex multi-holding / multiple strategy portfolio - 50+ different positions across multiple SIPP and ISA accounts. Like most who end up in this position, I started with a simple spreadsheet which then got more and more complex as the years went by. I reached a point where it was taking me a lot of effort to update on a monthly basis so that I could analyse and slice and dice to my hearts content. I reached a point where I had decided that I needed to start again from scratch and redesign a complex spreadsheet with automated price updating now that I was clearer on my needs. I started the redesign (I have a software development background from many many years ago). I reached a point when I had decided that essentially I would need to write an Excel or Access based app to meet my needs. As my coding skills were a bit out of date I began to think about learning Python as a bit of a hobby. Then one morning I woke up in a cold sweat because I could see this idea starting to consume my limited free time and seriously asked myself if it was a wise move.

I then went on the quest you have embarked on and I tried to find an off the shelf solution which did what I needed it to do. After lots and lots of searching and trialing of alternates (including MS Money, various spreadsheet based solutions etc) I came across Fund Manager. I played around quite a bit with the free download and satisfied myself that it had the functionality I needed. I also created the portfolio history I needed in spreadsheets and tested out the transaction upload functionality quite a bit and satisfied myself that it worked well. From memory I think I came to the conclusion that I would need to write some simple input spreadsheets to help me download transactions from iWeb and YouInvest but that would be much simpler and better than writing an app from scratch or rebuilding a complex spreadsheet solution. I could finally see a route through this.

I was about one or two weeks away from taking the plunge and going all in with Fund Manager. I finally had in my grasp the solution I’d been looking for. But something didn’t feel quite right. I had a nagging doubt. I started to re-examine why I had gone down this route. At the core of this was the following problem statement... “How can I reduce the effort it takes me to update my complex portfolio on a monthly basis so that I can extensively analyse its performance?”

What I eventually realised is that I had asked the wrong question and then found a solution to the wrong question.

Why was my portfolio so complex? What value was the complexity adding? Why did I feel the need to analyse my portfolio in so many different ways? Did this analysis improve my performance as an investor?

I reached the conclusion that I was running and experimenting with too many alternate investment strategies: HYP investing, baskets of ITs, ETFs for global diversification and alternate asset classes etc etc. My analysis up to that point showed me that the best of my strategies had done no better than a simple global ETF portfolio. The worst of my strategies had underperformed the same simple global ETF portfolio quite significantly at times. I didn’t need any more detailed analysis to tell me that I should invest in a simple global ETF portfolio as outlined in numerous investment books and blogs I had read over the years.

I switched my focus and switched my portfolio. I simplified massively. My only analysis now is tracking my path to financial independence. I created a new and much simpler spreadsheet which relies on simple end of month downloads straight from my SIPP and ISA accounts. My hours of effort at month end has changed to 30 minutes at most once a month which is a combination of downloading portfolio information and making semi-automated purchasing decisions based on asset allocation and portfolio balancing rules.

I have a feeling that most investors who go through a phase of treating investing as an interesting hobby and feel the need to analyse performance as part of their personal learning and education journey will travel down the spreadsheet or quest for a tool route. I’m not suggesting that you should abandon your quest. I’m simply suggesting that you should examine the fundamental question of what you are trying to achieve and why you feel the need for a portfolio analysis tool. If you think lots of analysis is going to give you ‘an edge’ then it’s worthwhile realising as quickly as possible that it isn’t! I mention this as your post said “I understand that knowing what performance your portfolio has achieved over time is a key component of good investing”. First define what you personally mean by “good investing”.

“You don’t need to be a rocket scientist. Investing is not a game where the guy with the 160 IQ beats the guy with 130 IQ.” – Warren Buffett

Best of luck with your continuing journey.

Degsy

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 209 times

Re: Best way to track portfolio performance

#293174

Postby torata » March 22nd, 2020, 1:09 pm

BitterLemon wrote:[
Hi Torata

Thanks for the suggestion. It sounds ideal.

I've just installed MS Money but it seems I need to key in each trade manually. I really wanted to be able to load a list of transactions - I have a lot as some of my providers do things like sell a tiny chunk of some holdings each month to pay fees and some income units get reinvested. I tried loading a csv file but it didn't seem to like it, only seems to want QIF or ofx formats. I just have data that ive copy pasted from my various platforms which I've started to standardise into one big csv. Am i missing something?


Hello BL

If you are looking to load up all previous transactions then yes, that would take a long time unless you can import.

When I started using MS Money in 2010, I only recorded the purchases of shares that I still had in my portfolios, and ignored everything else that had happened in years previous to 2010. I know that as a result I don't have an accurate annual return on, for example, Lloyds, as I didn't record the dividends that were received up to 2010, but good enough is good enough.

A quick search shows it is possible to convert csv to qif with a 3rd party extension to excel http://xl2qif.chez-alice.fr/xl2qif_en.php.
There also appear to be some online converters, e.g. https://less-accounting-120404.groovehq.com/help/convert-csv-to-qif.

HTH

torata

BitterLemon
Posts: 22
Joined: March 19th, 2020, 1:33 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Best way to track portfolio performance

#293452

Postby BitterLemon » March 23rd, 2020, 2:07 pm

Hi Degsy

Many thanks for your detailed and insightful post. I really enjoyed reading it.

I've started to look at fundmanagersoftware, I note that the brokers which are supported for direct integration seem to be US based which is a shame. I will do futher extensive testing & evaluation on it.

I have also gone down a route of looking at gnucash which seemed promising for a while but seems to be somewhat generalist and lack useful investment reporting functionality. People have built bolt-ons to add some of these missing parts but i would have to start getting my hands dirty with perl and python. Like you I also have a background as a developer but am not familiar with these languages and probably because I know enough about software development i know the likely time sink that they would be both initially and in ongoing maintenance.


It's a very good idea for me to reconsider what I am trying to achieve here.

Having given it more thought, there are 2 factors at play:
Firstly there is the evaluation of performance so that I can compare it against the return I could have made from paying off debt. This would then allow me to (hopefully) feel vindicated that I have made the right economic decision to invest thus far and give me a much needed boost of confidence and conviction to continue to do so. This is especially important in these uncertain times.

Secondly I have for many years neglected to put the time into managing my portfolio. As the size has grown over time so has the unease with this situation. This is not to say i have not put time in, but when i have it has been sporadic. Generally other factors in life have distracted. This is no doubt exacerbated by the number of different holdings/accounts/platforms. I would like to invest some time now in the creation/setup of a tool which i can then systemise to reduce the time it will take to gain insight and manage my investment portfolio in the future.

I have recognised some time ago that I have more holdings that I probably should. As you say this brings more complexity than is helpful and indeed beneficial. I could reduce the number of holdings and have been trying to for a while, it's a battle against an instinctive trait to spread my bets. This tendency has been reinforced by regret of poor performing funds I have held which then feeds back to reducing the commitment to the next fund I purchase.

There is probably a strong case for a one off assessment of performance to date since embarking on the investment strategy. My thinking is that since even the most quick and dirty approach will entail considerable effort I'd like put in a bit more effort now to have some that I can reuse for many years to come.

You have certainly got me thinking.

Degsy67
Lemon Pip
Posts: 96
Joined: November 4th, 2016, 7:32 pm
Has thanked: 76 times
Been thanked: 68 times

Re: Best way to track portfolio performance

#293837

Postby Degsy67 » March 24th, 2020, 9:40 pm

BitterLemon wrote:Hi Degsy

Many thanks for your detailed and insightful post. I really enjoyed reading it...
.
.
.
You have certainly got me thinking.


BL

Glad you found my input useful and thought provoking. I recognise myself in a lot of the things you’ve said, including the point around underlying portfolio complexity stemming from spreading your bets.

My journey included looking at gnucash. I reached the same conclusions.

I agree with you on the lack of direct support for UK brokers in terms of importing transactions directly into Fund Manager. I was initially concerned that the US focus may also mean that UK market pricing isn’t well supported however that doesn’t appear to be the case. I tested the software with typical HYP shares, ETFs and ITs, all of which were FTSE listed. I didn’t however test UK OEICs / Unit Trust funds as I don’t hold any. If you do then maybe check this out early. Maybe worthwhile reporting back here for the benefit of others on your findings. If the software looks likely to be worthwhile for you (and possibly others here) then I always felt that it would be relatively easy to write a spreadsheet macro which could download and reformat transactions from AJ Bell YouInvest, iWeb and other common UK brokers for easy upload into the Fund Manager software. Potentially some Lemon Fool community effort here?

On the broader question of simplifying your investing, I’d encourage you to spend time focusing on two sources of information:

1) Any and all Monevator articles which cover passive vs active investing, the under performance of fund managers, the impact of fund costs on investment returns, the Monevator simple passive portfolio.

2) Smarter Investing by Tim Hale.

If you want to reduce your effort and simplify your portfolio then you will get better investment results by spending time soaking up these two sources of information compared to using the same time to implement a portfolio management tool. Don’t get hung up on wanting to analyse the past. What’s happened has happened. Analysing it to death isn’t going to change the results. You can only affect the future, not the past. Draw a line in the sand. Take the total value of your portfolio now and pick a very simple benchmark such as a broad global stock market index ETF - eg, Vanguard FTSE All-World (VWRL). During the course of the year measure your total portfolio value against this index. There are only 3 possible outcomes:

1) Your portfolio underperforms VWRL - why are you putting all this effort in to underperform such a massively diversified equity index. Conclusion - move to a simplified low cost ETF based portfolio (see Time Hale / Monevator for details).

2) Your portfolio matches the performance of VWRL - why are you putting all this effort in to simply match the performance of such a massively diversified equity index. Conclusion - move to a simplified low cost ETF based portfolio (see Time Hale / Monevator for details).

3) Your portfolio outperforms VWRL. You spend hours looking at your fantastically detailed analysis. Through this you identify that some of your strategies / holdings / funds have performed exceptionally well vs VWRL, some are about the same and some have performed really poorly. Now you have a decision to take. What conclusions do you reach? What do you do with this incredible insight? What actions do you take? Here are some scenarios:

a) You conclude that you are a fantastic investor and you’ve discovered that for some elements of your portfolio you have ‘an edge’. Do you feel confident enough to dump your mediocre and poorly performing holdings and to go all in on the things which outperformed? Is that likely? If not then why have you bothered putting all this extra effort in? What value has it delivered to you?

b) You conclude that it’s too early to tell what works and what doesn’t work. You have no strong feelings. You decide to tinker a bit with your portfolio because doing something, doing anything, satisfies your desire to ‘actively manage your portfolio’. You’ll decide to tinker and then give it another year, then another year, then another year. Rinse and repeat.

c) You recognise that your results are fairly random. Even though there are some strategies / holdings which are clearly performing well, you still hanker after broad diversification to satisfy your risk profile. You therefore are highly unlikely to ever pick a smaller number of holdings to focus on. Your analysis is never going to get you to a point where you will change your behaviour and simplify your portfolio but graphing performance feels like active management so you simply keep doing it as you’ve already sunk the effort into automating this so you may as well carry on.

d) You recognise that your results are fairly random and that the effort you’ve put into analysis isn’t really creating much value and ultimately is making no significant positive difference to the performance of your portfolio. You spend a bit of time reading Monevator and Tim Hale. Then you decide to take action and simplify your portfolio.

If you want to justify investment vs paying off the mortgage then there is a shortcut. Compare your mortgage interest rate against the yield of VWRL. The current quoted dividend yield for VWRL is around 2%. A quick look at my own bank, First Direct, tells me that I could get a 2 year fixed interest offset mortgage for 1.99% with no booking fees. So long as you’re likely to have a mortgage for the next 5 to 10 years, there’s an extremely good chance that the income from VWRL will outstrip the interest rates on UK mortgages. Your only question therefore is how can you at least match the share price performance of VWRL? The answer to that is very simple. It doesn’t need a lot of fancy graphs and multiple holdings in your portfolio.

Best of luck

Degsy

TUK020
Lemon Quarter
Posts: 2039
Joined: November 5th, 2016, 7:41 am
Has thanked: 762 times
Been thanked: 1175 times

Re: Best way to track portfolio performance

#293888

Postby TUK020 » March 25th, 2020, 7:25 am

Degsy67 wrote:
BitterLemon wrote:Hi Degsy

Many thanks for your detailed and insightful post. I really enjoyed reading it...
.
.
.
You have certainly got me thinking.


BL

Glad you found my input useful and thought provoking. I recognise myself in a lot of the things you’ve said, including the point around underlying portfolio complexity stemming from spreading your bets.

My journey included looking at gnucash. I reached the same conclusions.

I agree with you on the lack of direct support for UK brokers in terms of importing transactions directly into Fund Manager. I was initially concerned that the US focus may also mean that UK market pricing isn’t well supported however that doesn’t appear to be the case. I tested the software with typical HYP shares, ETFs and ITs, all of which were FTSE listed. I didn’t however test UK OEICs / Unit Trust funds as I don’t hold any. If you do then maybe check this out early. Maybe worthwhile reporting back here for the benefit of others on your findings. If the software looks likely to be worthwhile for you (and possibly others here) then I always felt that it would be relatively easy to write a spreadsheet macro which could download and reformat transactions from AJ Bell YouInvest, iWeb and other common UK brokers for easy upload into the Fund Manager software. Potentially some Lemon Fool community effort here?

On the broader question of simplifying your investing, I’d encourage you to spend time focusing on two sources of information:

1) Any and all Monevator articles which cover passive vs active investing, the under performance of fund managers, the impact of fund costs on investment returns, the Monevator simple passive portfolio.

2) Smarter Investing by Tim Hale.

If you want to reduce your effort and simplify your portfolio then you will get better investment results by spending time soaking up these two sources of information compared to using the same time to implement a portfolio management tool. Don’t get hung up on wanting to analyse the past. What’s happened has happened. Analysing it to death isn’t going to change the results. You can only affect the future, not the past. Draw a line in the sand. Take the total value of your portfolio now and pick a very simple benchmark such as a broad global stock market index ETF - eg, Vanguard FTSE All-World (VWRL). During the course of the year measure your total portfolio value against this index. There are only 3 possible outcomes:

1) Your portfolio underperforms VWRL - why are you putting all this effort in to underperform such a massively diversified equity index. Conclusion - move to a simplified low cost ETF based portfolio (see Time Hale / Monevator for details).

2) Your portfolio matches the performance of VWRL - why are you putting all this effort in to simply match the performance of such a massively diversified equity index. Conclusion - move to a simplified low cost ETF based portfolio (see Time Hale / Monevator for details).

3) Your portfolio outperforms VWRL. You spend hours looking at your fantastically detailed analysis. Through this you identify that some of your strategies / holdings / funds have performed exceptionally well vs VWRL, some are about the same and some have performed really poorly. Now you have a decision to take. What conclusions do you reach? What do you do with this incredible insight? What actions do you take? Here are some scenarios:

a) You conclude that you are a fantastic investor and you’ve discovered that for some elements of your portfolio you have ‘an edge’. Do you feel confident enough to dump your mediocre and poorly performing holdings and to go all in on the things which outperformed? Is that likely? If not then why have you bothered putting all this extra effort in? What value has it delivered to you?

b) You conclude that it’s too early to tell what works and what doesn’t work. You have no strong feelings. You decide to tinker a bit with your portfolio because doing something, doing anything, satisfies your desire to ‘actively manage your portfolio’. You’ll decide to tinker and then give it another year, then another year, then another year. Rinse and repeat.

c) You recognise that your results are fairly random. Even though there are some strategies / holdings which are clearly performing well, you still hanker after broad diversification to satisfy your risk profile. You therefore are highly unlikely to ever pick a smaller number of holdings to focus on. Your analysis is never going to get you to a point where you will change your behaviour and simplify your portfolio but graphing performance feels like active management so you simply keep doing it as you’ve already sunk the effort into automating this so you may as well carry on.

d) You recognise that your results are fairly random and that the effort you’ve put into analysis isn’t really creating much value and ultimately is making no significant positive difference to the performance of your portfolio. You spend a bit of time reading Monevator and Tim Hale. Then you decide to take action and simplify your portfolio.

If you want to justify investment vs paying off the mortgage then there is a shortcut. Compare your mortgage interest rate against the yield of VWRL. The current quoted dividend yield for VWRL is around 2%. A quick look at my own bank, First Direct, tells me that I could get a 2 year fixed interest offset mortgage for 1.99% with no booking fees. So long as you’re likely to have a mortgage for the next 5 to 10 years, there’s an extremely good chance that the income from VWRL will outstrip the interest rates on UK mortgages. Your only question therefore is how can you at least match the share price performance of VWRL? The answer to that is very simple. It doesn’t need a lot of fancy graphs and multiple holdings in your portfolio.

Best of luck

Degsy


Degsy,
the majority of this post should be reproduced under its own topic in Investment Strategies.
Taking over from Luni for coherently reasoned and eloquently expounded of theses?
tuk020

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Best way to track portfolio performance

#312716

Postby scotia » May 27th, 2020, 5:04 pm

I hope I'm not going to sound too divisive, but unless you have relatively simple requirements, I think a spreadsheet is not the ideal tool - you would be better off constructing a database. I find Microsoft Access easy to use, and with Visual Basic as the associated language it is relatively simple to build a bespoke application - particularly if you have any programming background. You would keep each of your transactions (purchases, sales, dividends) in the main table and you can create simple routines to produce reports in a variety of forms - I usually output them to Excel. You can also update your current prices automatically using one of the methods outlined in the LemonFool - although it will require some small changes to the listed Excel form. Once up and running, you will only require to enter any new transactions, and at a press of a button you can create reports in your favoured form.

EssDeeAitch
Lemon Slice
Posts: 655
Joined: August 31st, 2018, 9:08 pm
Has thanked: 268 times
Been thanked: 251 times

Re: Best way to track portfolio performance

#312736

Postby EssDeeAitch » May 27th, 2020, 6:38 pm

BitterLemon wrote:
tjh290633 wrote:In my view the simplest way is to construct a cash flow table for your portfolio, based on cash in and cash out, ignoring cash arising inside the portfolio which stays in the portfolio. Dates in the first column, cash movements in the second column, positive for inflow and negative for outflow (or the current value) and apply Excel's XIRR formula to the table. This will give you a measure of the total return.

It's not hard to do, if you have the data available, and will be as good as anything that you will find elsewhere.

TJH


Yes I was considering something more straightforward vaguely along these lines but you've brought some method and clarity so thanks for that and the excel function - useful to know it exists.

I'm knee-deep in trying to get historical pricing which will allow more granular performance monitoring. It's a bit beyond my original intention but once you start investing time in these things your appetite grows! It's great to have your approach as a fall back though.


For overall portfolio performance tracking, I agree with TJH that XIRR is very good (it can also be applied to individual holdings as well as the whole pf) but I would also add Unitisation to the mix. This is also easy to set up and also takes into account pf movement as well as monies in and out of the pf. You can find assistance on Unitisation here http://lemonfoolfinancialsoftware.weebl ... folio.html

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Best way to track portfolio performance

#312969

Postby scotia » May 28th, 2020, 10:57 am

EssDeeAitch wrote:
For overall portfolio performance tracking, I agree with TJH that XIRR is very good (it can also be applied to individual holdings as well as the whole pf)

If you are keeping records over a long timescale, then it may be useful to download the Retail Price Index (RPI) table, and compute the XIRR corrected for RPI. It gives a better indication as to whether or not you are winning.
Alternatively you can use the Consumer Price Index (CPI) table - and the corrections will be somewhat less!

tjh290633
Lemon Half
Posts: 8208
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4096 times

Re: Best way to track portfolio performance

#313057

Postby tjh290633 » May 28th, 2020, 2:32 pm

scotia wrote:
EssDeeAitch wrote:
For overall portfolio performance tracking, I agree with TJH that XIRR is very good (it can also be applied to individual holdings as well as the whole pf)

If you are keeping records over a long timescale, then it may be useful to download the Retail Price Index (RPI) table, and compute the XIRR corrected for RPI. It gives a better indication as to whether or not you are winning.
Alternatively you can use the Consumer Price Index (CPI) table - and the corrections will be somewhat less!

If you want a comparison with the RPI there is no need to use XIRR. Unitised portfolio, either income or accumulation model will give you a direct comparison of both income and capital with RPI. It helps if you rebase them to a common value at the start point. Accumulation units give you a TR comparison if you are that way inclined.

TJH

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Best way to track portfolio performance

#313075

Postby scotia » May 28th, 2020, 3:42 pm

tjh290633 wrote:
scotia wrote:
EssDeeAitch wrote:
For overall portfolio performance tracking, I agree with TJH that XIRR is very good (it can also be applied to individual holdings as well as the whole pf)

If you are keeping records over a long timescale, then it may be useful to download the Retail Price Index (RPI) table, and compute the XIRR corrected for RPI. It gives a better indication as to whether or not you are winning.
Alternatively you can use the Consumer Price Index (CPI) table - and the corrections will be somewhat less!

If you want a comparison with the RPI there is no need to use XIRR. Unitised portfolio, either income or accumulation model will give you a direct comparison of both income and capital with RPI. It helps if you rebase them to a common value at the start point. Accumulation units give you a TR comparison if you are that way inclined.

TJH

Since I'm only interested in total return, I compute the TR XIRR (for individual funds and for the total portfolio), using dated purchases, sales, dividends and current market price - although with accumulation units I don't separate out the internal dividend with its date. And to compute an RPI-corrected XIRR I rebase all numbers to the relevant monthly RPI. It adds very little to the computational effort.
Since I keep all of the dated sales, purchases and dividends in my database, I don't need to adopt a formal unitised approach. I enjoy writing the code - and can produce a variety of reports at the press of a button!


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 8 guests