Record Real Time Data in Subsequent Rows

[farhan86a]

Dear all,

I am new to this forum So please pardon me if my words seems breaching the rules of posting. I am not an expert in Excel, not even close, especially when it comes to "writing" macros.

Problem: I have a stream of real-time data flowing in to excel via a formula. The data is the price of a futures contact which gets updated automatically in seconds in a cell. But I inferred that its basically of no use to me unless I crunch it more. And I don't know how I can get it done. Meaning I have to have the data recorded first in the subsequent rows as and when it changes in the first cell. To put it in steps, here is what I intend to do:

A B C
1 Time (hypothetical) A B
2 9:15:01 5001.50 1250000

The above would be the very first cell. Now, the moment the data changes I need a row to get inserted just below it which will have the same formula.

A B C
1 Time (hypothetical) A B
2 9:15:01 5001.50 1250000
3 9:15:02 5000.00 1253000

It should also be noted here that the first row must have only the value and hence subsequently. So at the end of the day I shall have a list of the data of every second on which further processing can be done. Was that too much? Apologize if it was. And any guidance/help will be immensely wonderful and I would be grateful.

Thanks again!

Farhan

 

[Saagar]

It might be easy to do it a different way, let me know if this works. Row 2 will have the formula and update dynamically as data changes. We can record data from row 3 onwards, values only. Let me know if that works.

Example
A B C
1 Time (hypothetical) A B
2 9:15:03 5001.50 1250000 (formula)
3 9:15:01 5000.00 1255000 (first second value)
4 9:15:02 5001.20 1251000 (second second value)
5 9:15:03 5001.50 1250000 (third second value)

 

[farhan86a]

Saagar,

Oh I thought nobody saw my poor post But thanks you gave it a look. Well yes anything that let me record that data will be wonderful even if it can start from the second row. Curious to know how can that be possible

Thanks a ton!

 

[Saagar]

Right click on the tab, and click on the "View Code" option. Paste this code as is, close the window and save the spreadsheet. Make sure "macros" are enabled (Tools > Macro > Security menu option in Excel 2003). It is in a different place in Excel 2010.

Private Sub Worksheet_Change(ByVal Target As Range)

capturerow = 2

If Not Intersect(Target, Cells(2, "A")) Is Nothing Then

    currow = Range("A65536").End(xlUp).Row

    Cells(currow + 1, 1) = Cells(capturerow, 1)
    Cells(currow + 1, 2) = Cells(capturerow, 2)
    Cells(currow + 1, 3) = Cells(capturerow, 3)

End If

End Sub

Anytime value in cell "A2" changes, the macro will record the values in the rows below. Test is out and let me know if it works.

 

[farhan86a]

I have Excel 2007 installed. So what I did was in the workbook I pressed Alt + F11 and pasted the code and saved the workbook. On re-opening it I enabled the macro but nothing happened. The price is getting updated in the cell only Or am I creating the macro in a wrong way?

 

[Saagar]

I don't know what exactly Alt-F11 does. It does open the code window but the highlighted window may not belong to the spreadsheet we need.

Please follow my directions. Right click on the tab name and use "View Code" option.

If you still want to use Alt-F11, make sure you select the spreadsheet in the "Project" window where you want to record values.

Is A2 changing every time the values in the row change?

 

[farhan86a]

Yes I did all as you said. But the data is just updating in that cell only. Its not getting recorded in the cells below. Does the macro gets initiated automatically when the sheet opens? Sorry to give you pains

 

[Saagar]

Try this and let me know what happens:

Private Sub Worksheet_Change(ByVal Target As Range)
capturerow = 2 currow = Range("A65536").End(xlUp).Row
Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 3) = Cells(capturerow, 3)
End Sub

 

[Saagar]

Figured it out. Since you have formulas in A2-B2, changes are not triggering the code. Clear all the code I gave you so far and use this instead:

Private Sub Worksheet_Calculate()
    capturerow = 2

    currow = Range("A65536").End(xlUp).Row

    Cells(currow + 1, 1) = Cells(capturerow, 1)
    Cells(currow + 1, 2) = Cells(capturerow, 2)
    Cells(currow + 1, 3) = Cells(capturerow, 3)
End Sub

Let me know if it works.

 

[farhan86a]

OMG it worked!! wow precisely what I wanted. Thank youray: a million ton! You are my hero now