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.

Vraag excel.PNG

Kind Regards Lauren

[ranman256]

you will make a form, put 2 text boxes in it:
txtStartDate and txtEndDate

put your data range here.

in the code change this to match your sheet.
here, Col Q will write the results of the date check. If the date is in the range it writes FOUND in the column,kFLAGfldLtr.
set it to a free column.
When the code finishes it filters the results.

Const kFLAGfldLtr = "Q" 'column for the flag field

add a button to start the macro.
the button executes: btnFindDates_Click()

Please Login or Register  to view this content.

[6StringJazzer]

Hi,

Welcome to the Forum !

Crossposted at

https://www.mrexcel.com/forum/excel-...lio-maker.html
https://stackoverflow.com/questions/...ortfolio-maker

Your post does not comply with Rule 8 of our Forum Rules: Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you must provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (most other forums have the same rule).

[Qontrax]

Thank you very much, im going to try to figur out what you wrote. If it worked I'll let you know.

[6StringJazzer]

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

[bakerman2]

Lauren, please post example file so we have something to work with.

[Qontrax]

I can't figure the answer of ranman 256 out so here is an example file as asked by 6StringJazzer. To make it clear again what the problem is: The records (column E till XXX) for the company mentioned in Column C should only be shown when the Date in Column D is between the dates in Column A-B.
I hope this helps solving the question.

Kind regards,
Qontrax

[JeteMc]

Seeing that this has not be solved on either of the other sites, here is a proposed solution.
1) The text values in columns A and B are changed to dates using the Text to Columns tool.
2) On Sheet 2 the following formula populates columns A:AF

Please Login or Register  to view this content.

3) On Sheet 2 columns A, B and D are formatted as dates.
4) On Sheet 2 columns E:AF are formatted as general and conditional formatting is applied to change the font of any zero value to white.
Let us know if you have any questions.
If this should be acceptable please be considerate and inform members of the other sites that the question has been answered.

[Qontrax]

Seeing that this has not be solved on either of the other sites, here is a proposed solution.
1) The text values in columns A and B are changed to dates using the Text to Columns tool.
2) On Sheet 2 the following formula populates columns A:AF

Please Login or Register  to view this content.

3) On Sheet 2 columns A, B and D are formatted as dates.
4) On Sheet 2 columns E:AF are formatted as general and conditional formatting is applied to change the font of any zero value to white.
Let us know if you have any questions.
If this should be acceptable please be considerate and inform members of the other sites that the question has been answered.

Firstly, thank you for trying to solve the question. I'll definitely delete the question on the other forums once this one has been solved. But there still stays a problem. In sheet one you can see that USG corp is only in the S&P 500 till 31/01/1992, still sheet 2 shows data till 2008. I wish i had any idea what went wrong but my knowledge is too limited :s.

Kind regards,
Lauren

[JeteMc]

I misunderstood. Try the following on sheet 3 in E2 (copied over and down to AF290):

Please Login or Register  to view this content.

Let us know if you have any questions.

[Qontrax]

JeteMC, I can't thank you enough for the help you offered. This helps me out enourmesly. I changed the code a bit because it still included the data before the date mentioned in column A and because I changed column D->A so i could freeze it. Ill post the code for if anyone should ever have the same problem

Please Login or Register  to view this content.

I'll also immediately delete the posts on the other forums .
Thank you a lot,

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.