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?
I jus tcreated a repeating macro that copy and pastes teh data. I don't reall do anything after that.
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
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
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?
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