Automatic portfolio maker

[Qontrax]

Hi,

At the moment I'm writing on my thesis which is about Return and Reversal. To do this I need to construct a lot of portfolio's. At the moment I have 1100 stocks which I need to evaluate. As you can see from column A to C I have the different companies who where in the S&P 500 and from when till when. Column D are the monthly dates for which every stock (column E till XXX) shows it's closing price. The problem is that columns E till XXX also show stock data from when these stock's werent in the S&P500. Now I'm searching for a macro, formula,... which auto selects only the data I need. For example, If i wanne make a portfolio from 1992-1997. I only want the data of USG CORP from 1992-1997 selected and not the rest of the data (until 2016). If I have to do this by hand it would take me at least 3 days. So any help is very welcoming.

Kind regards,
Lauren

 

[emukiss10]

Hello,

Can't You use Filter to select only dates You want?

 

[Qontrax]

Hello,

Can't You use Filter to select only dates You want?

The problem is if I filter in column D. I get all the data of companies who have data for the time period I selected, lets say 1992-1997. But what I need is only data for companies who where in the S&P 500 during that time period. Not all the stocks. And if I use a filter on column A or B then i only get the companies who joined/left the S&P 500 those year. But thank you anyway for thinking along.

 

[emukiss10]

As I understand, in column C there are only companies from S&P 500 or not only? If not, is there any indicator that company is/was in S&P 500?

 

[Qontrax]

As I understand, in column C there are only companies from S&P 500 or not only? If not, is there any indicator that company is/was in S&P 500?

Indeed Column C are the companies that are in the S&P500 en column A-B represent from when till when this company was in the S&P 500.

 

[emukiss10]

I really dont understand the outcome You want to achive. Mayby You can try to explain it a little clearer?

 

[Fluff]

Cross posted https://stackoverflow.com/questions/49533925/automatic-portfolio-maker
and https://www.excelforum.com/excel-programming-vba-macros/1225752-automatic-portfolio-maker.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Qontrax]

I really dont understand the outcome You want to achive. Mayby You can try to explain it a little clearer?

In columns A-B are the dates for each stock mentioned in column C when it was in the S&P 500. Column D are monthly time points from 1992-2016. E till xxx are monthly stock data for all the stocks mentioned in row 2. This data is for the time period in column D. The problem is that i have all the data for all these stocks from 1992-2016. But i only need the data from when they were in the S&P 500, being the time period mentioned in column A-B. I hope this is clearer.

 

[emukiss10]

ok, Im trying to crack this one open

IF date in colum D is between dates from columns A and B - You want to see those records - if not - You do not want to see them.

Is that correct?

 

[Qontrax]

ok, Im trying to crack this one open

IF date in colum D is between dates from columns A and B - You want to see those records - if not - You do not want to see them.

Is that correct?

My excuses I was on a vacation. But that is indeed correct. IF date in column D is between dates Column A and B i want the records of the company mentioned in C. I hope this helps you.