Backtest Correlation Dividends

November 28th, 2019, 03:43 AM

Hello all, Hope you are well,

I am a beginner in VBA & in modelling, that's why I'm asking for your help today.

I have a data base with 300 stocks annual dividends and monthly stocks prices. I have therefore made a vba graph (sheet "Base 100 Dividendes") comparing the stock price evolution of a stock (orange curve), with its dividendes evolution (blue curve). The histogram illustrates the dividend progression.
Problem: I am trying to create a model with a backtest. This model would try to find a correlation between the histogram's evolution and the stock price evolution. This model can be backtested on the all period or just 1, 2 or 3years. (I don't much knowledge in maths & stats).
Example: On the stock 7532 JP Equity, we can see that the stock price is positively correlated to the the histogram evolution.
I'm open to any solutions even though I'd like to stay on VBA Excel. Pls contact me in private if you try to send me an .xlsm file.

Thank you very much! have a nice day,

January 23rd, 2020, 09:46 AM

Hi,

I use Excel VBA for analysis and have made a few posts on here.

I don't consider dividends in my analysis, but have been sort of rethinking that. Theoretically, as long as adjusted prices are used, dividends can be ignored - certainly makes programming easier to do that. I'm slightly interested in total return analysis and have been considering including dividends; however it is a non-trivial amount of work to set up and the benefits are doubtful.

Your chart Base 100 Dividends is not convincing - stock prices and dividends are rising over time (16 years) but not sure what that suggests other than that the company (or whatever you are looking at) is viable.

Your arrangement of multiple securities prices horizontally in columns is a bit questionable. Each security should have its own worksheet. When a summary type display is necessary, that should be built from the individual detail security worksheets that one is interested in. The columnar arrangement is a design flaw that probably has to be corrected if you want to improve your analysis.

Correlation matrices are easy to set up (google - correlation matrix excel vba). In this case, you have a little advantage in that your data is already set up in columns.

InvestExcel is a decent place to get excel examples.

Good luck in your research, it looks like you have made a good start.

Backtest Correlation Dividends

Discussion in Stocks and ETFs

Backtest Correlation Dividends