PDA

View Full Version : Sleeper: Copy stock symbols from one sheet to another



Tenspeed39355
09-26-2005, 09:30 AM
I would like to take all the 650 stock symbols in Sheet 1 Column A2:A650 and copy them one at a time to sheet 2 column A2. I wil need a short delay of 3 seconds after each symbol has been copied. I will put in a formula from a program I have to look over into Yahoo and get some data I need which will be
put in Col F2. I then want to take what is in F2 and copy it to Sheet 1 Column B
2:B650. Can this be done. Thanks in advance for your time with this.
Max Parris:hi:

MWE
09-26-2005, 09:36 AM
I would like to take all the 650 stock symbols in Sheet 1 Column A2:A650 and copy them one at a time to sheet 2 column A2. I wil need a short delay of 3 seconds after each symbol has been copied. I will put in a formula from a program I have to look over into Yahoo and get some data I need which will be
put in Col F2. I then want to take what is in F2 and copy it to Sheet 1 Column B
2:B650. Can this be done. Thanks in advance for your time with this.
Max Parris:hi:
One can certainly program in a delay of 3 seconds from one copy/paste to the next. Based on what you want to do, I would suggest a slightly different approach. I would have the procedure do the copy/paste and then monitor some target cell for, say, anything other than a blank, and as soon as something appears, do the next copy/paste. That may be a little faster and would preclude initiating the next copy/paste before it should be done.

MWE
09-26-2005, 09:59 AM
further to my comments above, attached please find a sample spreadsheet that does the copy/paste with delay that you seek. The delay is presently set to 1 second; just change the value for SecDelay in the code. Further, the code selects Sheet2 as the active sheet so you can see the values being pasted (with delays). Also, the Status Line at the bottom of the Excel window displays what is going on.

Hope this helps a bit ...

Tenspeed39355
09-26-2005, 10:02 AM
How do I set up the program so that the symbol in Sheet 1 A2 is copied to Sheet 2 cell
A2 set the delay at that time for 2 seconds then the seconds symbol from Sheet 1 cell
A3 is copied to sheet 2 Cell A2 and so on until all the symbols have been copied?
I can do a copy and a paste but to do that for 650 symbols is to much. I think that if there is a way for a macro to accomplish this then we are cooking
Max

MWE
09-26-2005, 11:23 AM
How do I set up the program so that the symbol in Sheet 1 A2 is copied to Sheet 2 cell
A2 set the delay at that time for 2 seconds then the seconds symbol from Sheet 1 cell
A3 is copied to sheet 2 Cell A2 and so on until all the symbols have been copied?
I can do a copy and a paste but to do that for 650 symbols is to much. I think that if there is a way for a macro to accomplish this then we are cooking
Max
The spreadsheet provided in my earlier reply did a delayed copy/paste cell for cell so at the end all symbols from Sheet1 have been copied to their corresponding cell location on Sheet2.

If I understand your revised needs, you wish to:



copy Sheet1.A2 to Sheet2.A2
delay for 2 seconds
copy Sheet1.A3 to Sheet2.A2
delay for 2 seconds
continue for all items in ColA of Sheet1
The end result is a single cell on Sheet2 has received all elements from Sheet1. Just change the line
xlsheet2.Cells(I, 1) = xlSheet1.Cells(I, 1)
to
xlsheet2.Cells(2, 1) = xlSheet1.Cells(I, 1)


and SecDelay to 2




revised spreadsheet attached

Tenspeed39355
09-26-2005, 03:50 PM
Hi MWE That is just what I need. Here is the last part of the problem. When each symbol is copied from Sheet1 A2:A650 I am going to put in a formula in Column B cell B2 and Column C cell C2. The formula will go to Yahoo and get two numbers. One is the current market price and the other is the historical mkt price. Can you take the two
number and put them in Sheet 1 Columns B and C beside the symbol for each set of numbers.
Example:
When AOF is copied to Sheet 2 the number in Col B cell B2 and Col C cell C2 are
$10.00 and $13.00. I need the number to move to Sheet 1 Col B and C beside AOF
As each symbol is copied from Sheet1 to Sheet2 the numbers will change and they need to go from Sheet2 over to Sheet 1 beside the symbols. This will go down th 650 funds. I hope I have made this so you understand it.
I took out your ABCs and replaced them with symbols. I also changed the time to one
second.
Again thanks for your help. The first part works just fine.
Max Parris

MWE
09-26-2005, 06:29 PM
Hi MWE That is just what I need. Here is the last part of the problem. When each symbol is copied from Sheet1 A2:A650 I am going to put in a formula in Column B cell B2 and Column C cell C2. The formula will go to Yahoo and get two numbers. One is the current market price and the other is the historical mkt price. Can you take the two
number and put them in Sheet 1 Columns B and C beside the symbol for each set of numbers.
Example:
When AOF is copied to Sheet 2 the number in Col B cell B2 and Col C cell C2 are
$10.00 and $13.00. I need the number to move to Sheet 1 Col B and C beside AOF
As each symbol is copied from Sheet1 to Sheet2 the numbers will change and they need to go from Sheet2 over to Sheet 1 beside the symbols. This will go down th 650 funds. I hope I have made this so you understand it.
I took out your ABCs and replaced them with symbols. I also changed the time to one
second.
Again thanks for your help. The first part works just fine.
Max Parris
Max: glad to help.

Copy/pasting the values from Sheet2.B2 and Sheet2.C2 back to the "correct" location on Sheet1 would not be hard, but synchronization will be important. For example, what happens if it takes longer than 1 second to fetch values from the web due to some slowdown in your link, etc. As mentioned earlier, it may make more sense to monitor Sheet2.B2 and Sheet2.C2 for a change in value and then post the values back to Sheet1 and do the next iteration.

I will provide a modified spreadsheet (later today) that approachs the problem either way and you can then play with both to see which works better for you.

Tenspeed39355
09-26-2005, 07:27 PM
I understand what you are suggesting about monitoring Sheet2.B2 and Sheet2. C2 and then post back to sheet1. If you are willing to send both ways I will test each and see which is best. Again thanks for all your time with this
Max

MWE
09-26-2005, 07:59 PM
I understand what you are suggesting about monitoring Sheet2.B2 and Sheet2. C2 and then post back to sheet1. If you are willing to send both ways I will test each and see which is best. Again thanks for all your time with this
Max
Max: attached are approaches:

DelayedCopy3.xls uses a fixed delay and then copies Sheet2.B2 and Sheet2.C2 back to Sheet1. There is some dummy code in the VBA that writes some text to Sheet2.B2 and Sheet2.C2 after the delay so there is something to copy back.

DelayedCopy4.xls uses a "test for something different in Sheet2.B2 and Sheet2.C2" and then copies Sheet2.B2 and Sheet2.C2 back to Sheet1. If the procedure detects "no difference", it waits for 0.25 seconds and looks again. If it were to continuously look, it would hog the system and not allow any cycles for the real web fetch. There is some dummy code in the VBA that simulates the functional calls to the web. The function WebSim simply delays for a second and then passes back something for Sheet2.B2 or Sheet2.C2. Thus the total simulated time to fetch both web values is 2 seconds.

Play with both approaches and see which works better (I suspect version 4).

Tenspeed39355
09-27-2005, 04:51 AM
Good morning MWE I think you have it just like I need the macro on both ss.
I need one more change to make it work for the symbols. You have put in some webvals
that need to be changed so the macro will work for the symbols. You have the following
"Webval1 for". I will try to explain what needs to go in there. I have a program that looks
over into Yahoo or MSN and retreives the current price or the historical price. The formula for the price is =xlqprice(A2) and the formula for the historicla price is
=xlqhclose(A2,$B$1). The $B$1 is the historical date which is put in Sheet2 column B
B1. What needs to be changed in the macro so this will give me the numbers I need?
Is the change made with the "Webval1 for" and "Webval2"?
Thanks again for your time and help with this
Max

MWE
09-27-2005, 07:00 PM
Good morning MWE I think you have it just like I need the macro on both ss.
I need one more change to make it work for the symbols. You have put in some webvals
that need to be changed so the macro will work for the symbols. You have the following
"Webval1 for". I will try to explain what needs to go in there. I have a program that looks
over into Yahoo or MSN and retreives the current price or the historical price. The formula for the price is =xlqprice(A2) and the formula for the historicla price is
=xlqhclose(A2,$B$1). The $B$1 is the historical date which is put in Sheet2 column B
B1. What needs to be changed in the macro so this will give me the numbers I need?
Is the change made with the "Webval1 for" and "Webval2"?
Thanks again for your time and help with this
Max
Max: I modified version 4 to handle what you have outlined above and tested it with some dummy versions of xlqprice and xlqhclose. I simulated the time it might take for these functions to actually fetch something from the web by calculating the sqrt of a number a million times (this will run faster or slower on your machine based on its CPU and memory). The values returned by the two procedures is a random $ amount between $0 and $100. Seems to work fine.

BUT, based on a little testing, it appears that the delay method I have been using, i.e., Application.Wait has two problems:
1. it seems to lock up Excel for the wait
2. if does not work for delays less than 1 second
These problems mean that the constant value delay approach will probably not work -- during the wait or delay, Excel is effectively halted and it is unlikely that the xlgprice and xlghclose functions will do anything; and the "keep looking until something has changed" approach will also probably not work (as presently implemented with the 0.25 sec delay). An alternative may be to simply use the "keep looking" approach with no delay. I believe that this will allow Excel to execute the xlgprice and xlghclose functions correctly while VBA is "spinning its wheels". When both xlgprice and xlghclose have fetched new values, the VBA proc will see that and index to the next stock. This is what Version5 does

To use Version5 with the actual xlgprice and xlghclose, simply delete the dummy versions of those procedures.

Tenspeed39355
09-28-2005, 04:54 AM
Hi MWE I ran the ss you sent, version5. I changed the A,B,C etc in Sheet 1 col A to
the stock symbols and ran the ss. Here is what I am seeing. Each time I run the ss
the numbers are different. It looks like the macro is picking out random number.
I looked at the macro and saw where you encluded two random functions. I do not
understand why they are there because it looks like the macro is returning random numbers and not the real prices. I did not want to take the random part out until I talked with you.
The rest of the macro works just fine. The number are being copied from Sheet2 to Sheet 1 Col B & C. I tried to see if I could find out what was going on so I set up
one symbol in Sheet2 col A15 and ran the xlqprice(A15) in column B15. I kept comming
up with random numbers. LOL
Max

MWE
09-28-2005, 09:21 AM
Hi MWE I ran the ss you sent, version5. I changed the A,B,C etc in Sheet 1 col A to
the stock symbols and ran the ss. Here is what I am seeing. Each time I run the ss
the numbers are different. It looks like the macro is picking out random number.
I looked at the macro and saw where you encluded two random functions. I do not
understand why they are there because it looks like the macro is returning random numbers and not the real prices. I did not want to take the random part out until I talked with you.
The rest of the macro works just fine. The number are being copied from Sheet2 to Sheet 1 Col B & C. I tried to see if I could find out what was going on so I set up
one symbol in Sheet2 col A15 and ran the xlqprice(A15) in column B15. I kept comming
up with random numbers. LOL
Max
Max: please reread my comments about Version 5 and what you need to do. In particular, look at the very last line of the reply. As long as the dummy versions of those functions are in the current project (spreadsheet), the versions in the addin you got from QMatrix will not execute

Tenspeed39355
09-28-2005, 11:40 AM
hi MWE This is what you ask me to do; To use Version5 with the actual xlgprice and xlghclose, simply delete the dummy versions of those procedures.

Are you asking me to take out the =xlqprice(A2) out of Sheet2 Col B and take out of
Sheet2 Col C C2 which is =xlqhclose(A2,$B$1) or are you saying to take the "=xlqprice(A2) and "=xlqhclose(A2.$B$1) out of the macro?
Max

MWE
09-28-2005, 06:25 PM
hi MWE This is what you ask me to do; To use Version5 with the actual xlgprice and xlghclose, simply delete the dummy versions of those procedures.

Are you asking me to take out the =xlqprice(A2) out of Sheet2 Col B and take out of
Sheet2 Col C C2 which is =xlqhclose(A2,$B$1) or are you saying to take the "=xlqprice(A2) and "=xlqhclose(A2.$B$1) out of the macro?
Max
Neither. In order for the spreadsheet to do what you want, the formulas must remain in Sheet2.B2 and Sheet2.C2. Those formulas are written to those locations by the code in the macro DelayedCopyPaste

As mentioned earlier, I needed to write dummy versions of xlgprice and xlghclose in order to test the rest of the code. As long as those versions are in the spreadsheet code module any reference for those functions will be resolved by the dummy versions. What you need to do is delete the dummy versions of xlqprice and xlghclose from the VBA code in the spreadsheet:
1. open the VB editor
2. click on the code module associated with the spreadsheet (it is called Module2)
3. scroll down the code in the right hand window until you come to the functions xlqprice and xlghclose.
4. Select all lines for the two functions and hit the delete key. The relevant lines are:


Function xlqprice(TargetCell)
Dim I As Long
Dim X As Double
X = 1
For I = 1 To 1000000
X = X + Sqr(X)
Next I
xlqprice = Format(100 * Rnd, "$##0.00")
End Function

Function xlqhclose(StockSym, CloseDate)
Dim I As Long
Dim X As Double
X = 1
For I = 1 To 1000000
X = X + Sqr(X)
Next I
xlqhclose = Format(100 * Rnd, "$##0.00")
End Function

5. close the VB editor

The dummy versions of the two functions are now "gone" so when Excel tries to execute the formulas it will look around for the real version (which are in the AddIn you got from QMatrix)

Tenspeed39355
09-29-2005, 10:41 AM
Hi MWE I did as you ask and removed the above lines and ran the macro. I received an
error. If xlsheet2.Cells(2, 2) = OldB2 Or xlsheet2.Cells(2, 3) = OldC2 Then
You said that when the two dummy versons are gone excel tried to execute the formulas it will look around for the real version( Which are in the AddIn you got from QMatrix)
I do not understand the AddIn you got from QMatrix. The formulas are in Sheet2 Col B and Sheet2 Col C. =xlqprice(A2) & xlqhclose(A2,$C$1. What am I doing wrong?
Max

MWE
09-29-2005, 12:00 PM
Hi MWE I did as you ask and removed the above lines and ran the macro. I received an
error. If xlsheet2.Cells(2, 2) = OldB2 Or xlsheet2.Cells(2, 3) = OldC2 Then
You said that when the two dummy versons are gone excel tried to execute the formulas it will look around for the real version( Which are in the AddIn you got from QMatrix)
I do not understand the AddIn you got from QMatrix. The formulas are in Sheet2 Col B and Sheet2 Col C. =xlqprice(A2) & xlqhclose(A2,$C$1. What am I doing wrong?
Max
Max: Please clarify precisely what error you received when you ran the macro without the dummy procedures. What you provided does not help much.

When Excel examines the formulas in Sheet2.B2 and Sheet2.C2, it see functions (xlgprice and xlghclose) it does not recognize, i.e., they are not part of Excel's built-in functions. Things worked as long as I provided dummy versions of those two procedures. Excel could not find them in its own library, so it looked at the VBA stuff, found the procedures and was happy. I assume that you obtained an Addin from QMatrix (or a similar organization) that has these functions. They sell a package that allows you to fetch stock data from an Excel spreadsheet. Included in that package are the two functions. If you did not purchase such a package or service, none of this is going to work.

Prior to your original thread, were you able to get stock values when you did things manually?

Tenspeed39355
09-29-2005, 12:23 PM
Hi MWE Yes I have paid for the qMatrix program. The two formulas that I have been using manually work fine. Example if I use the =xlqprice(A2) xlq goes over to Yhaoo and gets the mkt price for whatever is in A2. If I have 650 funds and do what I call a pulldown on column B xlq brings over all the mkt prices for each symbol in column A.
Here is the error message. Run-Time error '13' Type mismatch
The following what is highlighted in yellow
If xlsheet2.Cells(2, 2) = OldB2 Or xlsheet2.Cells(2, 3) = OldC2 Then
Max

MWE
09-30-2005, 07:26 AM
Hi MWE Yes I have paid for the qMatrix program. The two formulas that I have been using manually work fine. Example if I use the =xlqprice(A2) xlq goes over to Yhaoo and gets the mkt price for whatever is in A2. If I have 650 funds and do what I call a pulldown on column B xlq brings over all the mkt prices for each symbol in column A.
Here is the error message. Run-Time error '13' Type mismatch
The following what is highlighted in yellow
If xlsheet2.Cells(2, 2) = OldB2 Or xlsheet2.Cells(2, 3) = OldC2 Then
Max
Max: given what you state above, I am lost why you need any macros, any delays, etc. Given that the copy/paste down a column works, the only advantage I can see to the macro is that it updates stuff only when you want. Is that the real goal here? If so, there are lots easier ways to do this. For instance:


Enter the correct formulas in Sheet1.B2 (xlgprice) and Sheet1.C2 (xlghclose)
Copy and paste the formulas down Sheet1 for all 650 stocks and let the cells update
Select B2 to C651, right click and select copy
Right click again and select PasteSpecial
Select Values and hit OK
The values pulled from the web are now in the right locations, but the formulas are gone
You can now do whatever you want and the values in ColB and C will not change. Generating a macro to do this would take less than a minute.