Writing streaming data to txt file

[jamesba]

I have data streaming in to excel from an external source. I would
like to capture this data every given interval (say 5 seconds) and
write it to a text file.
I know how to open a text file and write static data, however, I do not
know how to handle streaming data.

 

[dmthornton]

I'm curious... how are you streaming the data into Excel? Is the external
source streaming text data into the clipboard and then you paste it into
Excel?

You can use Application.OnTime to have Excel run a routine at a set time.
I've tested this out before with this code that might be useful. This routine
just writes a timestamp to a sheet and then schedules itself again to run 1
minute later:

Sub TestSchedule()
Range("a65536").End(xlUp).Offset(1, 0) = Now()
'set schedule to run in 1 minute
Application.OnTime TimeValue(DateAdd("n", 1, Now)), "TestSchedule"
End Sub

 

[jamesba]

thanks for your response. The data streams in from an external stock
market quote provider (reuters).
after some tinkering I decided to use the Workbook_SheetCalculate()
function to log everytime new data comes in.

 

[John.Greenan]

Are you pulling in data from Reuters using DDE? If so, this proposed
solution will not work very well under load - more that 300 or so RICs and
you'll see performance degrade.

You cannot really rely on COM events like Workbook_SheetCalculate() for fire
when you think they should. This will work ok for a small number of stocks.

RTD is a better mechanism, but I you may have to work abound the sheet
calculate by adding a dummy formula (I don't think Workbook_SheetCalculate()
fires for RTD updates unless they trigger a calculation).

The best way to do this is either through a genuine streaming database such
as Vhayu, K, FAME or suchlike but they are very expensive.

As a cheap alternative, write a VB application that listens to the TIB /
RMDS and streams the events to a file - that will work better and be less
flaky than an excel spreadsheet.

Good luck