Hello, I'm new to the forum, I hope somebody can help me. I will try to be as descriptive as possible, if I need to give further info please let me know, I will answer straight away.
Column A: Names of the companies (stocks)
Column B: the one year return for that company
Column C: P/E ratio of the company
What I need to do is:
1.- Sort all the companies from the lowest to the highest P/E ratio (column C)
2.- Get the average and median of each of the quartiles, in other words, first take the top 25% of the companies in the list and do an average of their returns (column B) after do the same for each of the remaining quartiles.
3.- Do t-tests and F-tests between for the diferences of mean and median between the 1st and 4th quartiles only.
So basically I only need to obtain mean and median of each quartile and the p-values of the t-tests and the F-tests between the 1st and 4th quartile.
I attached one example of the table I'm trying to get.
Basically, I have doing it using sort, then counting how many companies are there each year to determine the boundaries for the quartiles and then obatining means and medians. Finally I'm doing the data analysis to get the p-values for differences of means and medians. The problem is that it is really taking a lot of time, I have been doing it this way for almost three weeks and I til have a lot of data to go.
So I was wondering if there is a way to do it using macros or VBA. If it is possible please let me know.
Thanks a lot in advance!
Bookmarks