Software for managing investment portfolio

cloud_dog

RD69 wrote: »

I tried MSMoney but it kept calling up the mothership to deliver personalised adverts, and it didn’t appear to have an answer to my equity forecast problem.

Just in relation to this it is easily achievable insofar as you set up a regular receipt of a divi (quarterly, biannually, monthly (whatever)).

You can then either visualise if or produce a report proving you with all the information relating to the category.

I appreciate that all software has a learning curve but I use MS Money because it provides 80% of what I need financially across, bank, credit cards, savings, investments, monthly reports, income reports, (yada yada).

I think MS Money will give you what you are looking for but that's not to say other options won't work just as well, i.e. Excel.

Re adverts.... The only thing that relates to that is that some of the menu options are hooked in to MSN pages but I never use them so never see any adverts.

Ainsley1

Well I am with Heedtheadvice's implied criticism regarding the OP here.

Op you say YOU are at the limit of what you can do in your original post not at the limit of what a Spreadsheet can do.
You are critical of potential lack of control that you apply (or by inference others may or may not apply) stating the (limited) controls built into some development environments, but by no means all, and tell us you would rather trust other software when you have absolutely no idea of whether their development and maintenance is controlled!

Probably not the area on this forum for such a discussion but it makes me wonder if you have a vested interest somewhere given the conflict in the logic of what you write! Maybe best starting a thread in the techie forum regarding programs and apps or on some specialist website.
:beer:
EDIT addition:
I note that you refer to difficulties comparing dividend forecasts with actuals but perhaps, as you suggest, there may be an answer that you are unaware of. Perhaps adding a specific example would help others to understand you issue there and help, if it is possible, to derive you a solution.

The latter would be something very relevant to this forum area.

JohnRo

I'm using open office calc to consolidate valuations and statements from three accounts, currently, then using the data collected to help build a visual picture of what's happening with various aspects of the investment to date and what might happen in future.

Nothing you've described sounds particularly complex, the key is obtaining the historic data you want to manipulate and of course visualising and knowing how to apply the required manipulation.

Any forecasting is guesswork and can only ever be a series of projections based on assumptions.

pafpcg

RD69 wrote: »

I'm restating this because I think if you try the dividend forecast problem in Excel (or similar) you may understand the reasons for me giving up using spreadsheets to manage a portfolio.

Sorry, I don't understand! What is this dividend forecast problem that you have? Why are are you so interested in comparing a forecast with the actual? If you are running a fund or investment trust, I could understand that you need to be able to accurately predict what the fund's cashflow will be in order to provide income distributions and for that you'd probably invest in a DBMS-based system, but for the typical MSE follower in a domestic situation with ISAs & SIPPS a simple spreadsheet showing for each equity the forecast and the actual seems adequate to me. In my circumstances, as well as maintaining a weekly update of portfolio values, I have a spreadsheet-based financial calendar looking forward 15-18 months where I itemise all dividends expected, direct debits, pension payments, credit card payments, transfers to regular savings accounts, etc. This is effectively my cashflow prediction that I use to plan when I will have cash available for further ISA contributions or to make another UFPLS drawdown from a SIPP, as well as balancing the primary bank accounts to ensure they stay in the black! For dividend forecasting, I just replicate the dividend payment from the previous year and update this if I can glean a more accurate date-of-payment or dividend value from reading the equity's annual report or from checking the HargreavesLansdowne web-site for upcoming dividends. Because the bulk of my portfolio is in investment trusts, the dividends are very stable - only 3i shows any significant variability. On receiving the dividend, I update the financial calendar spreadsheet, but I never conduct any forecast vs actual comparison. When it's time to do some dividend re-investment or additional investment, then I'll compare the equities from scratch - the reliability of the dividend payment will be only one of many variables considered. In any case, where a company makes a major cut in its dividend, I'm aware of this through news channels - if I wanted to divest myself of an investment because of a reduced (or increased) dividend, then waiting to find out until the actual dividend is paid would seem to be too late for me.

In summary, "managing my portfolio" is done by determining my financial requirements (investing/disinvesting) by looking at my cashflow predictions, jotting some numbers down on the back of an envelope and thinking about them, then looking at the table of my existing portfolio holdings for anything which should be sold and looking at publicly-available sources for what would best match my inestment requirements. In 25+ years, I've never yet used my own analysis for something such as dividend yield in preference to the data available from AIC/FT/HL etc.

Perhaps I'm misunderstanding what you mean by dividend forecasting?

I should stress that I'm not belittling your interest in making your comparison between forecast and actual dividends - we all have different interests and hobbies: whatever floats your boat......

TrustyOven

RD69 wrote: »

To summarise, from my point-of-view as a software developer, a Spreadsheet is in effect a programming system which entirely lacks any of the mechanisms that have been developed to support programming systems.

Write yourself a better system and sell it?

I mean, for this dividend forecast problem you talk about, won't you want to grab your debug-er (MSE seems to think this is a rude word??) and get into the open office source code and find the problem and fix it?

And add some unit tests. Then a lot of your issues would be solved, no?

RD69 wrote: »

no version control,

git add MySpreadsheet.MyExtension
git commit -m "New stuff!"

:P


Why is it important to forecast your dividends?
I did a quick thing like that in my spreadsheet the other week, but my forecast amount was 6 quid less than the actual received (ok that could have been my mistake though) - I didn't know what the fund was going to distribute per unit. So how can I forecast this? Does this mean it's wasted time to try?

RD69

Op you say YOU are at the limit of what you can do in your original post not at the limit of what a Spreadsheet can do.

Yes indeed. I said that ("limit") and still mean it. Criticising me for not using Excel is perverse.

My issues with spreadsheets are not the quality of the creator product (Excel, *Office Calc) but is with an individual's implementation of a software solution (i.e. a spreadsheet) with the code and the data all mixed up in same structure.



(From: http://www.math.harvard.edu/~knill/linux/problemswithspreadsheets.html - worth reading for the interested observer.)

You are critical of potential lack of control that you apply (or by inference others may or may not apply) stating the (limited) controls built into some development environments, but by no means all, and tell us you would rather trust other software when you have absolutely no idea of whether their development and maintenance is controlled!

Where did I say that? I would be very reluctant to invest in any software without some surety as to its provenance.

Probably not the area on this forum for such a discussion but it makes me wonder if you have a vested interest somewhere given the conflict in the logic of what you write! Maybe best starting a thread in the techie forum regarding programs and apps or on some specialist website.

Of course I have a vested interest... I'm trying to manage my investments. Or are you implying an ulterior motive?

For what it's worth I'll mention that software is a very investment based activity. The time one takes to learn a software product, using Excel to create a spreadsheet, are all investments of time.

In this context, building a spreadsheet is, for me, a dodgy investment.

I note that you refer to difficulties comparing dividend forecasts with actuals but perhaps, as you suggest, there may be an answer that you are unaware of. Perhaps adding a specific example would help others to understand you issue there and help, if it is possible, to derive you a solution.

Take a portfolio of equities and produce a monthly calendar that forecasts the total dividend income for the next 12 months.

Seems fairly clear to me. I could tighten the language by removing the word total.

That said, I'll post an example on this thread later.

bowlhead99

RD69 wrote: »

That said, I'll post an example on this thread later.

It would be good if you could, as the only thing you have mentioned you are specifically having trouble with is forecasting dividends and comparing with actuals. We know you want to forecast the dividends for a whole range of stocks and then be able to compare that number with another number. Depending on the number of stocks, some people would do that on paper or with Excel. Access could hold your data instead as you note.

Others above have suggested their personal solutions as using quite basic assuptions such as 'start by assuming the stock will pay the same as last year'. I expect others might consider what the stock paid in the previous few years and extrapolate a rate of change, but this can produce wild results without doing a lot of manual review of the companies' financial health. You can also research the financial calendars for most major stocks and see what their intended div announcements and payment dates are, to refine the date from last year.

If you were working with a whizzy system at a broker as a buy-side analyst, you would form a view on what each stock would pay based on consensus profit expectations (and your own profit expectation), dividend cover / payout ratios, historic trends, strength of last reported balance sheet, guidance given by the company in its news release and so on. Most home users looking to guess the amount of income they might possibly receive next year, and then key in the actual figure to see if it was higher or lower than the estimate, do not want to invest in a system and a team of employees that try to conduct independent analysis on all the stocks, because brokers and fund managers spend millions on their systems and data feeds, and that's beyond the reach of the home user.

So, assuming you don't want to buy a financial model from a broker or fund manager, or build your own system to have something calculate a projected dividend off you inputting ten years of raw company financials and a huge amount of fuzzy logic to predict a company's next move, the question comes back to the same thing as any person asks when starting on a procurement or development process. What exactly are the requirements?

RD69

Write yourself a better system and sell it?

I did. Exited. Portfolio.

I mean, for this dividend forecast problem you talk about, won't you want to grab your debug-er (MSE seems to think this is a rude word??) and get into the open office source code and find the problem and fix it?

That doesn't actually make sense. There is no financial management tool in OpenOffice and there is no fixing of the spreadsheet concept.

Methinks you may be doing some liquid extraction.

git add MySpreadsheet.MyExtension
git commit -m "New stuff!"

gitk
'binary files differ'

Yes, that'll do?

(Though some of the new XML based files might be better.)

Why is it important to forecast your dividends?

I'm going to post a generic example a bit later this morning. Hopefully this will give you some insight into why I want to do this.

Regards

cloud_dog

RD69, can I ask why you are trying to 'predict' your future dividends?

The reason I ask is that divi's are not guaranteed and in some cases are at the mercy of exchange rates on the day.

bowlhead99

cloud_dog wrote: »

RD69, can I ask why you are trying to 'predict' your future dividends?

The reason I ask is that divi's are not guaranteed and in some cases are at the mercy of exchange rates on the day.

Of course, nothing is guaranteed but if you have a large and broad portfolio and are trying to make financial plans involving how much cashflow you might get from year to year or what tax allowances it's sensible to use, the plans will be way off if you just 'err on the side of caution' and assume £0 for dividend income.

Beyond that it's just the accuracy of the forecasts (extrapolated from historic data, company announcements or broker projections) which will vary widely from one person to the next.