Hello. I was wondering how people are managing their portfolios. What softwares are they using to keep track of everything? Have you they bought something? Or have they devised their own? Do they use Excel, GoogleSheets? Have people programmed something using Python or R or anything else?
Also, wondering what would an ideal portfolio software management look like?
I would also tinker around and maybe write some code for it.
Out of interest, what do your excel portfolios do that the Stocko folio doesn't? I'm wondering what analysis I'm not doing, that I perhaps ought to be - I'm only using Stocko for my porfolio management.
Is it the level of granularity - i.e. the performance of each share as opposed to the portfolio as whole?
It sounds like a lot of work - perhaps I don't have enough time or am just lazy - but I just treat the whole portfolio as one large basket, only occasionally getting rid of stragglers when its obvious they need to be removed. I'm not sure having lots of data on individual shares would improve my own performance.
I use an Excel spreadsheet I designed myself. I record trades, dividends and expenses and track performance at individual share level, as well as portfolio level. I also have a portfolio dashboard that looks at valuations, profits/losses, IRR, consensus forecasts, expected news dates and dividend dates/amounts for all my holdings.
It's quite low maintenance, since I would never hold more than twenty shares and typically only do a few trades each month (and often none). I can only track performance on a monthly basis (or longer period) but have no interest in looking at a more granular level anyway.
All-in-all, it is significantly superior model to what is currently available through Stockopedia (though I say it myself!), I control the direction and speed of development and it does not tie me to a particular subscription platform.
The main advantage over the Stockopedia portfolio is the rolling stop loss. If a share goes up by 50% then down by 25% Stockopedia will show a 12.5% gain, whereas my spreadsheet will also show a 25% decline from the maximum price. I may then decide to sell the share.
I also find a monthly record of total SIPP and ISA values going back 8 years very useful for evaluating my performance.
I sort of look at trailing stop losses using the Price vs 52w high in Stocko folios - for my active portfolios, there aren't too many shares which I have held for over a year. Its not perfect, but I can quickly get a feel for when to sell (using this and other factors)
Always good to hear what others do. thanks john2.
I have a Google Sheets spreadsheet that I've been working on for about 10 years. Started off quite simple but now can track things like dividend history, portfolio allocation, IRR/TWR, and historic Sharpe. Best thing is the GOOGLEFINANCE function and the ability that I can update it anywhere with a web browser.
I too use google sheets. I input every share buy with; ticker, cost, number if shares, fees etc. asmwjackson mentions, there is an excellent feature which tracks the live share price automatically. So for each share entry I have a cell with the code =GOOGLEFINANCE( ? ), in the bracket you select the ticker you want to follow. This feature works 100% with US stocks, but I have found it doesn't pick up all UK listed shares. still it's great to have up to date live lives and an automatic up to date valuation. I then input the sell price and date and do a monthly win/loss, profit/loss ratio. etc
Like others I use a spreadsheet (Excel in my case) as my main tracking tool. I update it 'periodically' and always and the end of each month. I track my performance using CAGR and an Adusted Profit / Loss ratio.
I also keep a trading journal, and research notes, in EverNote. EverNote is very similar to OneNote, but supported iOS before OneNote did. I actually prefer OneNote but can't face moving everything over now.
I include a daily(ish) heatmap (see my heat map thread) of all my holdings in the trading journal.
Finally I use a little dashboard, developed in Python (JupyterLab and plotly) which I include in my trading journal every day to visualise my financial exposure by Industry (position size and profit / loss), and also the balance by Stockopedia Quality Rank (against homebrew 'sentiment' and 'relative strength' measures).
As you can see I have a bit of a mixed bag at the moment - and am probably a little over-exposed to Financials and Miners! I'm also starting to get a little worried by Griffin Mining (LON:GFM) which has turned from a profit to a loss over the last couple of months, but not enough to do anything about it. Yet...
As a Mac user, I have a Numbers spreadsheet - like Googlefinance, Numbers can pull up a range of share attributes automatically. As an Excel user, it took me a while to get used to Numbers, but it has some attributes that I really like now.
I use it for three things:
1) to split my portfolio into sub-portfolios
2) to better track dividend income and
3) Most importantly for me, to separate out holdings by when purchased and price paid. I occasionally buy something that I think is cheap as a temporary holding and it drives me nuts that most portfolio tools just add those holding in with existing holdings to give you an average price / profit etc.
Thank you. That sounds marvellous. The Python Program also seems very interesting. I don't know (and I know this is a big ask) if you would share your program or if you can point us in the direction of some tutorials etc. which you can look at to try and develop something.
The Plotly sunburst chart documentation is here: https://plotly.com/python/sunb...
I use JupyterLab to run the Python as it's a nice interactive environment. The documentation for getting started with Plotly in Jupyter is here: https://plotly.com/python/gett...
You'll also need to get the data you want into a pandas dataframe first, e.g. from a CSV file or Excel Spreadsheet, there's plenty of examples around if you Google, for example https://datatofish.com/import-...
Exporting a portfolio, or screen, from Stockopedia is a good starting point for the data (though I pull mine from my own database which I've been developing for many years).
On my (long...) todo list is to write a 'visualisations in Python' type post for Stockopedia but it's way down the list and I'm not sure how much interest there would be?
Good luck with your own endeavours!
Warm regards,
greensiax
Thank you do much. This is so helpful. Just a quick last question - do you know if you can display the results of python and plotly on the web - can you make it a web-based application?
It's not something that interests me so I've never tried. However JupyterLab renders in HTML in a browser and you can certainly save an interactive Plotly figure as HTML, using fig.write_html(), so I would imagine that you can.
There used to be a google.finance.co.uk site which had good portfolio features and comprehensive history analysis. Then they changed it to the present site which borders on the useless.
I was experimenting with GOOGLEFINANCE earlier with a view to using it to feed values into excel. The values seemed to be strangely unrelated to what my realtime broker feed was giving me (no I can't seem to screenscrape that, I've looked). A few google queries indicated that when GOOGLEFINANCE does update it returns the last trade price, bid or offer. Currently, after hours, the price is indeed the last trade price. The least inaccurate price feed from it seems to be to use "priceopen" which is a mid-price and so also not ideal for valuations. As always you get what you pay for.
I use excel - got a cheap license (£10'ish for Office professional) because the company I worked for qualified for the employee home usage scheme - worth checking out but they only seem to do Office 365 these days, and its now a discount to the regular price so not so advantageous.
On top of that I've added Randy Harmelink's excellent SMF excel plug-in
This is a quite comprehensive plug-in but with a US bias, and I only scratch the surface by using only the scrape function.
A quick cut and paste from a cell in my spreadsheet :-
=RCHGetWebData("https://query1.finance.yahoo.com/v7/finance/quote?symbols=RENX.L", "regularMarketPrice",6,20)
this means search yahoo finance for Renalytix, on its web page, search for the string "regularMarketPrice" and extract the 6 characters, 20 characters after the search string.
Occasionally the string needs tweaking - say a cross over from 1000.0 to 999.9 means we only need 5 characters instead of 6, but these problems are easy to spot and fix.
Anyone got other recommendations for getting live prices into excel ?
Every week I copy and paste the weekend totals to the separate HYP top up spreadsheet
http://lemonfoolfinancialsoftw...
I don't do top up's at the moment (living off the divi's) but it is useful for comparing my returns against the market - (extra columns added to unitise the cash withdrawals)
Personally I don't trust web site based solutions because they change - as epo001 indicated, I had everything in google finance until they "improved" it and buggrd everything up, sorry Stocko, but your portfolio's are of a similar concern - I'll keep my raw data in Excel - where it can be exported anywhere.
"Anyone got other recommendations for getting live prices into excel ?"
I get live prices into Excel by using the built in web query function to do a simple Google search for the share price for a specific stock. The problem with this is it brings in a table with most of the contents of the Google search page. So then I have a series of formulae that filter out the unwanted stuff to leave me with only the useful data like share price, quote time, previous close price, change on day etc.
It was a bit tedious to set up but it does give me live prices at the push of a button.
The SMF plug-in that you posted the link for, looks very interesting. I'm wondering if it could be modified to use Google search to get live prices rather thant he delayed prices from Yahoo finance.
I use Google sheets for tracking share prices in several broker accounts on one page. That uses mostly Google data, but I have it set to get data from alternative sites where Google does not provide data or if there is an error.
I'd like to do something with Excel as a back up, just in case google ever get bored with sheets.
I also use Google sheets for my main watch list as I can set a target price for each stock, then I can set a ratio of price/target price and colour code it so I can see at a glance which are closer to my buy price. Is also easy to add notes etc.
I get live prices into Excel from Sharepad. They have an "Export Live Table" feature, paste the table into your spreadsheet and then every time you save the spreadsheet it will update.
I have been using Excel for 11 years not found anything that gives me the flexibility.
