recording dynamic data in excel

D

dcunited

So I have a dde link where I pull quote data onto a spreadsheet, and I'm trying to see if there is a way to chart that data, but not sure if there is a way to record the 'ticking' data in excel. I did an excel search, and didn't see anything pertinent. (at least from what I could tell)

Is this possible?
 
Quote from dcunited:

So I have a dde link where I pull quote data onto a spreadsheet, and I'm trying to see if there is a way to chart that data, but not sure if there is a way to record the 'ticking' data in excel. I did an excel search, and didn't see anything pertinent. (at least from what I could tell)

Is this possible?
More...

check out the OnCalculate function in the worksheet object in the VBA.
 
Quote from dcunited:

So I have a dde link where I pull quote data onto a spreadsheet, and I'm trying to see if there is a way to chart that data, but not sure if there is a way to record the 'ticking' data in excel. I did an excel search, and didn't see anything pertinent. (at least from what I could tell)

Is this possible?
More...

you can attempt to chart it in excel but we tried this and it was messy and did not work very well so... we did the following...

Step One...
Create VBA Timer Event in Excel that will collect your Cell info (1 or more as many as you want to chart) for whatever second interval you want...

Step Two...
Take the data you have collected in this VBA timer event and do one of two things...

a. Throw it into Sierra Charts intraday file using code to correctly place it in the right format and Sierrra Charts will chart it perfectly on every update in a disconnected setting... (We have it doing custom indexes for the SP500 all day... watching the sp500 AD Decl versus the NYSE AD Decl line is very enligtening as how one could miss the start of moves in the ES using the NYSE AD Decl)...

or...

b. Throw the data collected from the Excel Timer Event into Access or SQL Server or some database and get a charting component from a company that will allow you to pull data onto it from that database using ADO code... the a) choice requires you know some intricate structural windows coding... b is the easier but more involved way...

hthelps...

cj...

:)
________________
HAVE STOP - WILL TRADE

If You Have The Vision We Have The Code
 
Unfortunately, there is no way to use a worksheet function (something that responds to an event such as a cell value change) to automatically trigger some VBA code that will save tick changes and update a chart.

There may be some 3rd party add-ons that do this, but you can approximate it by using a timer to go off every few milliseconds to check time stamps and if there is a change to update a table or worksheet with the new data.

There is an alternative if your data provider supports an activeX control that raises an event when there is a tick change. This can be loaded into excel and code written to capture every tick or price change.
 
Here is a complete sample of the windows Declare function for the VBA Timer event and the VBA code itself in three parts...

<b>Declares</b>

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

<b>VBA Timer Code...

Sub StartTimer</b> ()
On Error GoTo Err_Prob

TimerSeconds = 2 'pop the windows time every x seconds
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)

Exit_Err_Prob:
Exit Sub

Err_Prob:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Err_Prob

End Sub

<B>Sub EndTimer</B>()
On Error GoTo Err_Prob

runTimer = False
KillTimer 0&, TimerID

Exit_Err_Prob:
Exit Sub

Err_Prob:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Err_Prob

End Sub

<B>Sub TimerProc</B>(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error GoTo Err_Prob


DO YOUR CODE HERE TO GRAB THE CELL INFO YOU NEED…


'you also need error handling since pulling and pushing data burps once in awhile...

Exit_Err_Prob:
Exit Sub

Err_Prob:
If Err.Number = 55 Then
'take some code action here...
Resume Next
Else
Err.Clear ' Clear Err object fields
Resume Next
MsgBox Err.Description
End If
If Err.Number = 440 Or Err.Number = 432 Then
'Tell user what happened. Then clear the Err object.
MsgBox "There was an error attempting to open the Automation object!"
Err.Clear ' Clear Err object fields
Resume Next
End If
EndTimer
StartTimer
Debug.Print Err.Number & " " & Err.Description
Resume Exit_Err_Prob
End Sub

You build a macro button on the front interface of Excel that calls the start timer event and a button that does the End Timer event and -- bada bing... you got a charting system...

htHelps...

cj...

:)
______________________
HAVE STOP - WILL TRADE

If You Have The Vision We Have The Code
 
Quote from Bernoulli:

Unfortunately, there is no way to use a worksheet function (something that responds to an event such as a cell value change) to automatically trigger some VBA code that will save tick changes and update a chart.
More...

Sure there is. Just use the Worksheet_Calculate event. Then you can do what you need to there. In regards to updating the chart, just make sure the chart refers to a named range. Then when you update the ticks, and add a cell to the place where the chart is getting it's data, reset that named range to the original range plus the new cell of data and the chart will update automatically.

Anything other than this, such as using a timer function will introduce a delay and possibly missed ticks.

Cash
 
Quote from cashonly:

Sure there is. Just use the Worksheet_Calculate event. Then you can do what you need to there. In regards to updating the chart, just make sure the chart refers to a named range. Then when you update the ticks, and add a cell to the place where the chart is getting it's data, reset that named range to the original range plus the new cell of data and the chart will update automatically.

Anything other than this, such as using a timer function will introduce a delay and possibly missed ticks.

Cash
More...

I see your point. But there is no target so how do I tell what cell is affected? Doesn't it just tell me that a cell somewhere on the sheet has recalculated?
 
Quote from Bernoulli:

I see your point. But there is no target so how do I tell what cell is affected? Doesn't it just tell me that a cell somewhere on the sheet has recalculated?
More...

What does it give you in the Target parameter that is passed in? That should be the cell that changed (it's a range variable)... of course you want to make sure that it is the price cell that you are looking for that is being updated.

Cash
 
Quote from cashonly:

What does it give you in the Target parameter that is passed in? That should be the cell that changed (it's a range variable)... of course you want to make sure that it is the price cell that you are looking for that is being updated.

Cash
More...

That's my point. There is no target parameter.
 
You must log in or register to reply here.
Back
Top