Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Excel Programming | |||
Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Charts and Charting in Excel | |||
Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Setting up and Configuration of Excel | |||
Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Excel Programming | |||
Cannot Activate Chart Area in Chart. Chart Object Failed | Excel Programming |