Welcome to NexusFi: the best trading community on the planet, with over 200,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- discounts are available after registering.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
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,
Can you help answer these questions from other members on NexusFi?
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.
