VBA Backtest Dividend Correlation

• 11/30/2019 at 8:46 PM #113973
  fizuaal

  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). I’m trying to say the that the model should kepp or sell the value depending on parameters.

  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. (My existing Code is in the last sheet becuz I can’t upload an .XLSM file here)

  Thank you very much! have a nice day,

  

  fizuaal

  Participant

  Topics: 1

  Replies: 1

  Been thanked: 0 times
  12/01/2019 at 9:34 AM #113985
  Nicolas

  Hi, you should have a look at Spearman rank correlation formula (Wikipedia: https://en.wikipedia.org/wiki/Spearman%27s_rank_correlation_coefficient)

  I believe it is massively documented on the web on the way to implement it into Excel VBA. Good luck 😉

  

  Nicolas

  Keymaster

  Topics: 81

  Replies: 20828

  Been thanked: 4742 times
  12/03/2019 at 2:37 PM #114123
  fizuaal

  Hi Nicolas,

  Thanks for your help but I know how calculate the correlation, I just don’t know how to implement it on VBA!

  

  fizuaal

  Participant

  Topics: 1

  Replies: 1

  Been thanked: 0 times
• Author
  Posts