• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering.
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Coming soon.
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models.

Backtest VBA Dividend Correlation

fizuaal

New Member
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). & this model aims to say if we sell (depending on the period & parameters) or keep a stock.
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.

Thank you very much! have a nice day,
 

Attachments

  • test (1).xlsx
    1.4 MB · Views: 4

AppliedMath

Active Member
Correlation is scaled co-movement. Your dividend and your stock don't have the sample sampling period rate do they? I don't think you can make a Covariance and thus correlation without this. That would be one thing to modify.

By histogram, I suppose to you are trying to compare the distributions of the dividends to the distribution of the stock price. That's different. If you can simulate the dividend distribution, then (again) you can (find and) compare it to the stocks distribution. But to do this, you need to "fit" each to some (likely) parametric function that generates them.

Dividends tend to follow stock prices. Are you trying to be able to plug-in a dividend value to some function/program, and get back an estimated stock price, or the other way around? A few of your aims seem misaligned. You should short them out one by one, and tackle them one at a time. Finally, in my completely biased opinion - don't use VBA.
 

longgamma

Well-Known Member
C++
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). & this model aims to say if we sell (depending on the period & parameters) or keep a stock.
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.

Thank you very much! have a nice day,
Are you including dividend yield in the calculation as well? High dividend yields aren't necessarily a good thing long term. Either market doesn't expect future dividends to increase or the company is actually handing out cash and not investing in capex and R&D.
 
Top