Automatic portfolio maker

Qontrax

New Member
Joined
Mar 28, 2018
Messages
6
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

kbVsA.png
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
I really dont understand the outcome You want to achive. Mayby You can try to explain it a little clearer?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top