Hi,
I have an Excel sheet where you directly see the quotes of the NYSE. I have made a formula to create a Z-value which tell me to (in my opinion) buy or sell.
The Z-value is changing every second, because the quotes of the stock market are changing every second. Now I want to record the given Z-values for example every minute or every 5 minutes.
Unfortunately it is not possible for me to build a macro who automatically record the give Z-values including a specific date and time.
For example, it has to look like this:
Day Time Value
30-6 10:01 0.4687
30-6 10:02 0.4568
30-6 10:03 0.4396
30-6 10:04 0.4402
30-6 10:05 0.4508
30-6 10:06 0.4893
30-6 10:07 0.5013
Do you have any idea how to create this?
Best Regards,
Evert van der Hoorn
If the Z-value will be shown in a certain cell , e.g. in cell K12, you can make a macro using application.ontime to copy that value in a certain column/sheet.
Post an example of your workbook so we can see where the z-value is; and please indicate where you want the different values of z to be stored.
Here you find a view of the file.
The green colored Cell BQ44 is the Z-value that changes every second.
Can you do something with this?
Thanks in advance!
Here is e better picture
You could use this macro:
Values will be written into column BS
Put this macro into the macromodule of the sheet that contains the cell BQ44.Sub start1() ' in werkblad Application.StatusBar = DateAdd("N", 1, Now) Application.OnTime CDate(Application.StatusBar), "Blad1.start1" Cells(Rows.Count, "BS").End(xlUp)(2) = Format(Now, "dd-mmyyyy hh:mm:ss ") & range("BQ44") End Sub
Adapt "Blad1' to the codename of the sheet.
Last edited by snb; 06-30-2011 at 07:03 AM.
Thanks! It is working!
Is there also a possibility that the macro continouisly record the data from cell BQ44 for example every minute?
The code I suggested does exactly do that.
Ok I always got the message after 1 minute that he can't execute Blad1.start1 even if i make a test version.
You can see it in the picture.
I used in this piocture the following macro:
Sub start1() ' in werkblad
Application.StatusBar = DateAdd("N", 1, Now)
Application.OnTime CDate(Application.StatusBar), "Blad1.start1"
Cells(Rows.Count, "E").End(xlUp)(2) = Format(Now, "dd-mmyyyy hh:mm:ss ") & Range("D5")
End Sub
I fear you didn't put the macro in the correct place. See the attachment.Evert, zet svp even codetags rondom je vba-code.
Open the attachment and wait several minutes. See what happens.
If you want to stop it run macro 'stop1'.
Last edited by snb; 06-30-2011 at 11:38 AM.
It's working now! Thanks for that!
Is it also possible that the output will come in 3 different Columns? For example BS, BT and BU.
Please add code tags in your post #8.
Adapted code:
Sub start1() ' in werkblad Application.StatusBar = DateAdd("N", 1, Now) Application.OnTime CDate(Application.StatusBar), "Blad1.start1" Cells(Rows.Count, "BS").End(xlUp)(2).resize(,3) = Format(Now, "dd-mmyyyy hh:mm:ss ") & range("BQ44") End Sub
Thanks again. Is it also possible to have it like this:
BS BT and BU.
Thanks again. Is it also possible to have it like this:
BS (date) BT (time) and BU (z-value).
No I have in every cell all the values!
You got what you asked for
Amended code:
Sub start1() Application.StatusBar = DateAdd("N", 1, Now) Application.OnTime CDate(Application.StatusBar), "Blad1.start1" Cells(Rows.Count, "BS").End(xlUp)(2).resize(,3) = split(Format(Now, "dd-mm-yyyy hh:mm:ss ") & range("BQ44")) End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks