VBA Chart
 

Hi All

I have a spreadsheet that records market data at 1 second intervals,
for between 15 - 30 minutes. The sheet uses 100+ columns and upto 1800
rows (30*60), I want a dynamic chart to display the data as it comes
in. Using XL2002, I set a chart using a defined name offset formula I
found on John Peltiers website, amongst others. Problem is, it cant
complete the graph and crashes my code which stops the data updates. I
think the data is coming in too fast as its fine if I manually input
dummy data into the range.

Does anyone have a slick workaround or is this just out of XL's
capabilities? Is there a free/shareware graphing add-in that could
cope?

Thanks in advance.

VBA Chart
 

Every second could be tricky. You could try stopping calculations/
screen updating while you update the values in Excel, then recalculate
after all the values are populated for that observation.
Application.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
....do the updates here...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

I'm also thinking that you might be better off updating the range via
code or actually updating the chart series references rather than
using an offset with a named range. The dynamic named range is fancy,
but it might be faster to allow the code to "hard code" the series
reference after each update.

VBA Chart
 

Jason

Thanks for your response.

I can see the benefit in turning screen updates off until after the
next data point has been added. I have a number of calculated
averages, max and min values and so on in some of the columns. Would
switching the Calculation to Manual and back impact on these?

As to updating the chart series by code rather than a dynamic range, I
assume I'd have to find the row numbers/range reference and assign
them to variables to pass to the chart sub. Do you know if that would
update the chart automatically or would I have to call the chart sub
again to refresh the chart?

VBA Chart
 

When you turn off calculation, it also turns off the chart updates.
Also, when you turn off calculation, it would affect all the
calculated min/max/avg values.

You might be able to turn off the calculations, update all the values
from your market data source, then turn on calculations (this would
update the min/max values you might be using to determine your chart
ranges), then update the chart. If you have a bunch of series to
update, you might want to turn off automatic calculation, then just
force a manual calculation after retrieving the data, update all the
series, then turn on automatic calculation again. The code for the
chart would be something like this...
ActiveChart.SeriesCollection(1).Formula = "=SERIES(Sheet1!
R1C2,Sheet1!R3C1:R6C1,Sheet1!R3C2:R6C2,1)"
By the way, what are you using for your market data source? I've
never gotten Bloomberg to update that quickly.

VBA Chart
 

Jason

Thanks for that. I'll give it a whirl and see how it goes.

It's not the stock market, it's sports markets on Betfair via an API
into XL. The main trading period is in the 10-15 mins leading up to an
event and into the "in play" period. Data refreshes in milliseconds
from the Betfair server to the API, so 1 second is quite slow
comparatively. You need a nimble mind and even nimbler fingers to
trade them which is why I want to get a visual representation of the
data rather than a rapidly changing screen full of numbers. Often by
the time you've checked certain numbers, check a couple of graphs in
the API, interpreted what they may indicate, etc etc, the price
movement has peaked/troughed and is on its way back!!!