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

 

[farhan86a]

You open to help me out in the development of a system with considerations? Please if you can give me your email ID so that I can elaborate more.

 

[Suyaash]

Hi Saagar,

The code really works great.

However, presently the data gets updated at each tick and stored in subsequent row.

I want the price data to get stored at each 1 min interval in subsequent rows.

Example
A B C
1 Time (hypothetical) A B
2 9:15:00 5001.50 1250000 (formula)
3 9:16:00 5000.00 1255000 (first second value)
4 9:17:00 5001.20 1251000 (second second value)
5 9:18:00 5001.50 1250000 (third second value)

Can you help me by revising the above code?

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.

 

[much8795]

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.

Hi Saagar,

I have the method to record real time data same as you advised above coding but if I have different method on column A1 as below;

A B
1 time RT data
2 10.01 20

Column A : I have use function "=now()" on this column as format hh:mm and I need to record data on every minute as time series.
Column B : Is the Real time data (I have simple mathematics calculate from real time data that I get from server.)

The records data like below;

A B
1 time RT data
2 10.01 20
3 10.02 18
4 10.03 19
5 10.04 22
6 10.05 25
.
.
.

I will take the data for graph plotting as real time again.

I would appreciated you kindly advise for the coding on VBA on Excel.

Fyi: My Excel version is on Microsoft Office 365


Best Regards and Thank you.

 

[skpiplani]

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.

Hi Saagar, it is quite old post, but i found it the same as the problem I am facing too, tried the above code, but it just keep copying the same info on the cell(A2) l in below A2, although there is no change in the 'formula cell value - A2'

 

[skpiplani]

Hi could you get the code to run.. please share it, I have a similar issue..