Sleeper: Using a Macro to get fund data

[Tenspeed39355]

I am using a formula that gives the Relitive strength index of any funds.The formula at J160 gives me the number I need to put in column B in sheet1.
How do I get the number from J160 to move to column B in sheet1?
So far I have linked all the fund symbols in Sheet 1 Column A to Sheet 2 Column
A1. When I run the macro the symbol in Sheet 2 column A1 changes. As it changes the number I need in J160 changes but I can not move the number in
J160 to move to sheet 1 Column B1 then the new number in J160 to move to B2
and so on. Can you give me some help with this one. Thanks
Max Parris:hi:

[Zack Barresse]

Hi Max,

Not sure where your data is. You specify two sheets, but don't distinguish between them. If you're talking about with code, maybe something like this ...


Sheets("Sheet1").Range("B65536").End(xlUp).Offset(1).Value = Range("J160").Value

This also does not take into account what sheet J160 is on, which I'd specify, as if you don't it will assume it's on the activesheet.

Does that help any?

[Tenspeed39355]

The fund symbols are in Sheet 1 and the rsi formula is in sheet 2 column J160
Sheet 1 looks like this.
A B
1 ACG
2 AOF
3 AWF
4 BIF
5 BTF

I want to move the data from sheet 2 J160 to this sheet and put the data in
column B. I start the macro from here by going to Tools Macro and run.
I can send you the macro as I have it if that will help.
Max

[Zack Barresse]

.. I can send you the macro as I have it if that will help.

Can you post it to the board?

[Tenspeed39355]

Here is the macro. I used the recorder to get the macro. I put in the wait command just to see the macro run


Sub Macro1()
' Macro1 Macro
' Macro recorded 02/04/2005 by Max Parris
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Range("A7").Select
End Sub

[mdmackillop]

Hi TenSpeed,
Is it something like the attached you are after. It copies each value from a range of cells into a target cell in turn, and returns another value based upon it to the adjacent cell of the data range.
MD



Sub GetResult()
For Each cel In Range("MyData")
Sheets("Sheet2").Range("A1") = cel
cel.Offset(0, 1).Formula = Range("Result")
Next
End Sub

[Tenspeed39355]

I will download it and get back to you after I run it
Thanks a load
max

[mdmackillop]

Here's a wee function to simplify your Wait method. Add the DoWait code as a separate sub and call it with the line "DoWait x", where x is the delay in seconds that you want.


Sub Test()
Tm = Format(Now(), "hh:mm:ss")
DoWait 3
MsgBox "Time up! " & Tm & " - " & Format(Now(), "hh:mm:ss")
End Sub

Sub DoWait(Secs As Integer)
Application.Wait Now() + Secs / 24 / 3600
End Sub

[Tenspeed39355]

Hi I tried you code but was having prolems with it. I hope this will explain what the
problem is. I have 450 funds symbols in column A In column B I want to put a number
from Tab 2. I have used the recorder to link each symbol in column A to A1 in Tab 2. When I run the macro the number I want in comes up in Column B1 which is good. Now here is the problem. When the macro goes to B2 the NEW NUMBER comes up correctly.
The problem is the number that was in B1 now becomes the number in B2. This goes on until all the numbers are beside the symbols, but they are all what the last number is. How can I get the numbers for each symbol to stay the same.
Example of what I see
AOF 6.35
ACG 6.35
ASG 6.35


This is what I want it to look like
AOF 6.35
ACG 3.25
ASG 10.00
I appreate any help on this.
I can not send the ss as I am using a program that changes the number I am after in Tab two when the market price changes. I know the number is changing because
lets say the first symbol is AOF, the number comes up beside it but when the new
symbol comes up and the new number comes up the first number changes to the new
number in B!.
Thanks in advance.
Max

[mdmackillop]

Hi Max,
If you could post the spreadsheet, even non-functioning, it would help my understanding.
MD

[Tenspeed39355]

I have never uploaded to you so how do I upload to you

Max

[mdmackillop]

First Zip your file. then click on Go Advanced below the Quick Reply box, Near the bottom of that screen, there's a button to Manage Attachments; Follow the instructions from there.

[Tenspeed39355]

I have ziped the ss and it should be with this e-mail
If you need anything please let me know The number I need for each symbol is
on Sheet 1 at J255. As the macro goes down the list that number changes and
I was each changed number to go with the new symbol
Max

[mdmackillop]

I can't see what would cause this. I've rejigged the spreadsheet to show the relevent cells etc. perhaps you could set up yours in similar fashion to keep an eye on what's happening.

[mdmackillop]

Hi Max,
I've adjusted the code to suit your spreadsheet. The code should return the value from the last cell in column J, as this appears to change daily (I hope that's correct). For Demo purposes, I set this cell to a vlookup function based on the yellow cells, to return some changing results.

[Tenspeed39355]

I am still having a problem with what you sent. Lets try the following.
Would you please make up a macro that will start in Sheet2 A1 and go to A25.
The stock symbols will be in that range. When the macro starts going from A1 to A25
each time it moves to the next cell it will also change the symbol in Sheet1 A1. Each time
Sheet1 A1 changes I want Sheet1 J255 to enter the new number in Sheet2 B1 and when
Sheet1 A1 changes again the new number in J255 will go to Sheet2 B2. I can get the symbols in Sheet1 A1 to change the problem I am having is that each time the numbers in J255 change the number in Sheet2 B1 changes to the new number. The old number will not stay in B1. If I can get the number in B1 and B2 to be different numbers that will solve the problem. I have sent you the ss before. Hope you can help me with this problem.
Max

[mdmackillop]

I'm at a loss here!
When I run the code in my last zip file I get the following result

ASG 42.1151
AOF 54.64999
ARK 57.14831
AWF 62.59569
BHK 63.91999
BIF 71.52099
CNN 55.36975

Which is what the Vlookup returns in J255, based on the yellow cells.

[Tenspeed39355]

That is what I get also. I have over 400 symbols to get the J255 number to go in. How do I do that. I tried typing in a new symbol with no luck.
Max

[mdmackillop]

How is the number in cell J255 changed when a new entry goes into cell A1? Is there another programme running which does this? Could it be that the the two programmes are out of synch? you could add in a delay as above.
Add the sub


Sub DoWait(Secs As double)
Application.Wait Now() + Secs / 24 / 3600
End Sub

and add the line "DoWait 1" after For Each Cel In MyData

[Tenspeed39355]

Good morning . Here is what I did. I put the wait commands in. Ran the macro.
I could see Sheet1 A1 change symbols, at the same time I can see the J255 number change. The symbol change for each symbol I had in Sheet2. I had a 5 sec wait put in.
I can also see in Sheet2 B1 the first J255 number when in but when it came to the second number going in to B2 the new number went in as planned. The problem is that B1
changed to the new number. I can see J255 change and hold for as many times as needed. I can not get the number in B1 to stay put.
You asked what was changing Sheet1 A1. I did a link between sheet2 A1 to Sheet1 A1
I did a link for each symbol in Sheet2 to Sheet1 A1.
I am using a program called XLQ. XLQ is in the price of the fund column. When the
symbol changes xlq goes to Yahoo and gets the new Mkt price and that changes J255.
I know we are close to getting this resolved. I still would like for you to write a macro
showing me how to make Sheet1 A1 change when the macro goes from Sheet2 A1:A5
The way I am doing it takes a lot of time. Can a Loop or for next command do it.
Thanks for your time with this.
Max

[mdmackillop]

I think i see the problem. The macro code is far too quick for your data retrieval. In my sample, Excel is responiding instantly to the cell A1 change; going to Yahoo takes time. I'll investigate if the macro can be suspended until cell J255 has changed in value. I foresee a problem, however, if two codes return the same value, but let's worry about that later.

[mdmackillop]

Next attempt.
I've changed the loopup routine to refer to today's date, otherwise the wrong values may be returned. This needs to be corrected (Omit the -3) for live use.
I've built in a loop to cycle until cell J255 changes, with a time delay (15 secs) before it times out. This should get out of the "same value" problem.
You can test the code by showing sheet 1 and sheet 2 together on a split window, and manually changing the values in J255


Option Explicit
Sub GetValues()
Const TimeDelay = 15
Dim MyData As Range, Cel
Dim Result As String, MyDate As String
Dim TmpVal As String
Dim St As Double
Sheets("Sheet1").Select
'Note Mydate set to use J255 value
MyDate = Format(Now() - 3, "d-MMM-yy")
Result = "Sheet1!" & Range("A:A").Find(What:=MyDate, After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Offset(0, 9).AddressLocal
Debug.Print Result
Sheets("Sheet2").Select
Set MyData = Range("A1", Range("A1").End(xlDown))
For Each Cel In MyData
'Get initial value
TmpVal = Range(Result)
Sheets("Sheet1").Range("A1").Formula = Cel
'Loop until a change occurs; Timed out after TimeDelay value
St = Timer
Do
DoEvents
If Timer - St > TimeDelay Then TmpVal = "Timed Out"
Loop Until TmpVal <> Range(Result).Value
If TmpVal = "Timed Out" Then
Cel.Offset(0, 1) = TmpVal
Else
Cel.Offset(0, 1) = Range(Result)
End If
Next
End Sub

[Tenspeed39355]

I found the problem with the numbers in the RSI ss. Let me refresh your memory.
When the macro started it would bring over the first RSI number and put it in B1. When the next number ran the new RSI number when in B2. The problem was that the number in B1 changed to the new number. Here is what I was doing that did not work.
I went to Sheet1 J255 did a COPY, left Sheet1 and when to Sheet2 B1 and did a COPY, PASTE SPECIAL, LINK. I would stop at that point. Here is the fix. When to
Sheet1 J255 and did the COPY, left and when to Sheet1 B1 and did a COPY, PASTE
SPECIAL, LINK, moved the curser to anothe cell, came back to B1 and did a COPY,
PASTE SPECIAL, VALUE and stopped. That fixed the problem. I did the same for all the fund symbols. The macro ran without a problem. I ran across this someplace and think
it is the answer. Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
It looks like that for each symbol there is a ton of repetive work to do for 450 funds.
If we have the answer to the big problem my question ot you is can we do just a short peice of macor so that when we finish with one symbol the loop goes to the next symbol. Again let me thank you for all your time.
Max

[mdmackillop]

When you paste a value into Cell A1, what triggers the programme that contacts yahoo? How long does it take for the value in J255 to change, after entering the new code in A1?

[Tenspeed39355]

Let me see if i can explain. The fund symbols are in Sheet2 A1:A6. I did a link between
Sheet2 A1:A5 to Sheet1 A1 for each fund symbol. When the macro runs the macro changes Sheet1 A1 to the new symbol. The RSI value in J255 is changed at the same time. I can not give you a time. Let me say that there is not delay. The program that contacts Yahoo is a program named XLQ. Here is how it works. In Sheet 1 C1 I have the command xlqhclose($A$1,A2), this sends the command to Yahoo to look for the closing price on the date in A2 and the closing price in $A$1. Hope this is helpfull.
Max

[mdmackillop]

Hi Max,
I downloaded a trial copy of XLQ and copied your Sheets 1 & 2 into it; I then input your formula as above and ran the code (latest version). It took time to run, with all the time outs, but I ran it again and the correct results appear to be displayed. I adjusted the time out to 1 second and ran the code with a fresh set of data. Time outs occurred for each item, but much quicker, and a second running produced the desired results. The demo file is attached for your perusal.
I'm away for a few days now, so can't respond until Monday. Basic thing is, the code works!
MD

[mdmackillop]

Hi Max,

Update on the last to avoid running code twice.



Option Explicit

Sub GetValues()
Dim MyData As Range, Cel
Dim Result As String, MyDate As String
'Pass data to cells to obtain data
Set MyData = Range("A1", Range("A1").End(xlDown))
For Each Cel In MyData
Sheets("Sheet1").Range("A1").Formula = Cel
Next
Sheets("Sheet1").Select
'Note Mydate set to use J255 value
MyDate = Format(Now() - 11, "d-MMM-yy")
Result = "Sheet1!" & Range("A:A").Find(What:=MyDate, After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Offset(0, 9).AddressLocal
'Pass data to cell to return data
Sheets("Sheet2").Select
Set MyData = Range("A1", Range("A1").End(xlDown))
For Each Cel In MyData
Sheets("Sheet1").Range("A1").Formula = Cel
Cel.Offset(0, 1) = Range(Result)
Next
End Sub

[Tenspeed39355]

I found the problem. The number in J255 is a formula. I did the following and that solved the problem. I started by doing a copy at Sheet1 J255, then when to Sheet2 B1 and did a paste special, link. The fix was that I moved the curser out of B1, then made B1 active again and did a copy, paste special and VALUES. I did this for each cell in Sheet 2 B1:B8.

[mdmackillop]

From the sounds of it, you're linking each of B1:B8 to the same cell, this will result in all cells showing the same value.
My code as tested using the XLQ programme, is returning the required results for 130 records, without having to carry out any paste link operations or other user intervention.