PDA

View Full Version : Finding data



Tenspeed39355
11-05-2005, 05:45 AM
Good morning guys. I have started using the MACD to locate fund that the
market price has started moving upward. The problem is the date that the
cross occors is at different dates in the ss. Example. In the ss the cross
will happen at some point in Column F. Is there a macro or other means to
locate the date the cross happens and put that date in sheet 2.
Also I will be tracking over 500 funds. I can put all the symbols in column A
in sheet 2 so that the cross date will show in sheet 2 column B. Since the cross
date appears at different places in column F would this be posable?
Thanks for your help with this.
Max:hi:

Ken Puls
11-05-2005, 11:01 AM
Hi Tenspeed,

Sure it would be possible, but I think we'd need some more information from you. Exactly how is the data arranged, what are you looking for, what would be a positive match vs a negative one... lots of questions.

Most certainly you could do it using VBA, but it may also be possible to acquire the information using a standard formula as well. Maybe a vlookup or index(match()) combination.

Depending on your answers, I can think that you may need to use a Find function, loop, autofilter, Vlookup or any one of a few more different routes.

Tenspeed39355
11-05-2005, 11:28 AM
thanks for your reply. I will try to answer your questions..The MACD is the result of
the 12 and 26 day moving averages. The MACD numbers are in Sheet1 Column D. The next column is E. That column has the trigger numbers. I am using a program xlq that has the formulas that look over into Yahoo for the results. In column F I am using the IF
command. IF(D2>E2,"Crossed","No") The symbol is in A1. I would like to be able to have
500 symbols in Sheet2! Column A. I want the macro to look in column E and find the date that the MACD data in column D is more that the data in column E for each symbol and to put the date that the cross is made next to the correct symbol.
A question for you. I have used VLOOKUP command before. What I am wondering is
how to use it in this application? How would it look to Column E and find the date for each symbol. NOTE: The cross date will be different for each symbol.
Max

Ken Puls
11-05-2005, 06:07 PM
Hi Max,

Okay, if I built your data correctly, then no. You couldn't use vlookup for this... actually, I shouldn't say that because someone might prove me wrong. ;) If I built you data correctly, you could use a real power formula to do the trick. Zack (firefytr) has a great article here on summing with multiple conditions. Funny enough, if you take it a step further than where he went, we can bend it to your use.

Now, one thing that I would HIGHLY recommend, is that if this is what you're after, name your data ranges. You'll find that it makes your formula so much easier to maintain, since it is pretty complicated.

I set up the table of data in sheet 1 and made up some bogues four letter stock symbols with some bogus data. I did use your formula to establish if they had been "crossed" or not though. The entire table covers A1:F11 (including headers).

Then I dropped a few of those same bogus symbols in column A of sheet 2, and put the following formula in cell B2. (FYI, just to make it fit the screen width, I broke it after every , in the IF formula. You'd want it all on one line in your cell.):

=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$11=Sheet2!A2)*--(Sheet1!$F$2:$F$11="Crossed"))=0,
"",
SUMPRODUCT(--(Sheet1!$A$2:$A$11=Sheet2!A2)*--(Sheet1!$F$2:$F$11="Crossed")*Sheet1!$D$2:$D$11))

This is a link to the article (http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=42). Basically it explains the first two -- sections of the Sumproduct formula, which gives us a value of 1 if the conditions are good. The extension is that we just multiply that 1 by the date to get the date value.

I've attached a copy of the workbook that I tested it on so that you can see it in action for multiple cases. :)

Hope it helps,