I have a worksheet that contains numerical data in cells F5 to something
like F20 or similar (depending on the event).

The data is refreshed from an external source (the time interval can be
adjusted by me).

Would it be possible to produce a graph below the data that would
refresh itself at an intervals of about 30-60 seconds or something set
by me, so that I could have a visual representation of events?

TIA

If you make a chart, doesn't it update with the data?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Saxman" wrote in message
...
I have a worksheet that contains numerical data in cells F5 to something
like F20 or similar (depending on the event).

The data is refreshed from an external source (the time interval can be
adjusted by me).

Would it be possible to produce a graph below the data that would refresh
itself at an intervals of about 30-60 seconds or something set by me, so
that I could have a visual representation of events?

TIA

Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

That's what I thought, but does it not overwrite the previous data, or
does the chart progress?

I'm asking you. I don't know the specifics of how your data is updated or
how your chart is defined.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Saxman" wrote in message
...
Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

That's what I thought, but does it not overwrite the previous data, or
does the chart progress?

Jon Peltier wrote:
I'm asking you. I don't know the specifics of how your data is updated or
how your chart is defined.

It is sourced from a website and put into a worksheet for me where it is
continually updated at a time set by myself.

Basically, it would be price on one axis and time on the other.

Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

It certainly is dynamic and updates the data, but I would like to view
changes in the data and not to get overwritten on a line graph.

For example, time on the x axis which could be triggered by the data
refresh. On the y axis would be different sets of data (cells F5 -
F20). One could then compare which set of data was
increasing/decreasing relative to one another.

So the new data overwrites the old? You could have a routine which copies
data after a recalculation event (which occurs when the data is updated) and
stores it onto another sheet. Then you can chart these blocks of data as
separate series on a common chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Saxman" wrote in message
...
Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

It certainly is dynamic and updates the data, but I would like to view
changes in the data and not to get overwritten on a line graph.

For example, time on the x axis which could be triggered by the data
refresh. On the y axis would be different sets of data (cells F5 - F20).
One could then compare which set of data was increasing/decreasing
relative to one another.

Jon Peltier wrote:
So the new data overwrites the old?

Yes, it is always being refreshed at a rate set by the user.

You could have a routine which copies
data after a recalculation event (which occurs when the data is updated) and
stores it onto another sheet.

This could be copy/paste with a timer?

Then you can chart these blocks of data as
separate series on a common chart.

Not sure what you mean as a novice?

Thanks for the feedback. This has been an age old problem of mine. At
least you have given me a guideline. For a novice, these problems are
horrendous, as there is virtually no material available, even in decent
Excel VBA books.

I have tried using macros before doing a copy/paste into another
worksheet, but they just seem to crash.

Could you give me an example of a routine, so then maybe I could modify it?

Let's say you have two sheets, the "Data" sheet which keeps refreshing and
the "Archive" sheet where you will store old data.

You can set up an event procedure on the Data worksheet. Right click the
Data worksheet tab and choose View Code. In the module that pops up, choose
Worksheet from the left dropdown, and Calculate from the right. This code
appears:

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Delete the second pair of lines:

Private Sub Worksheet_Calculate()

End Sub

Now you need code. I don't know what your data range looks like, let's
assume A2:A11, leaving a label in A1.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

Worksheets("Data").Range("A1:A11").Copy
Worksheets("Archive").Range("A2").Insert Shift:=xlDown

Application.EnableEvents = True
End Sub

This inserts the new data at the top of the Archive sheet, just under the
label in A1. This occurs every time there's a calculation in the sheet,
which happens when the data is refreshed. The EnableEvents commands turn off
events in case another calculation is caused by the copy/paste operations,
so you don't copy the same data numerous times.

Set up your chart by defining some names. Go to Insert menu Names
Define. In the dialog, enter a name for the first named range and a formula
to refer to it:

Name: Values1
RefersTo: =Offset(Archive!$A$1,1,0,10,1)

This means start at the cell one below and zero to the right of A1 (that's
A2), and make the range 10 rows and 1 column in size (i.e., A2:A11). This is
the data range for the first chart series. Add more series as desired (I'm
doing five for this example):

Name: Values2
RefersTo: =Offset(Archive!$A$1,11,0,10,1)
Name: Values3
RefersTo: =Offset(Archive!$A$1,21,0,10,1)
Name: Values4
RefersTo: =Offset(Archive!$A$1,31,0,10,1)
Name: Values5
RefersTo: =Offset(Archive!$A$1,41,0,10,1)

These are five named ranges which will always refer to A2:A11, A12:A21,
A22:A31, A32:A41, and A42:A51, no matter that you keep inserting more data.
The last five blocks of data will always be shown in the chart.

Now create a chart. In step 2 of the Wizard, on the Data Range tab, clear
what's in the selection box, then select the Series tab. Click Add, then
enter this in the Values box:

=Archive!Values1

which refers to the first defined name above. Click Add again and this time
enter

=Archive!Values2

and repeat for as many sets of Values as you've defined.

Now you can copy the chart and paste it onto the Data sheet (it will still
refer to the defined data on the Archive sheet), and you will see the
current and several recent sets of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Saxman" wrote in message
...
Jon Peltier wrote:
So the new data overwrites the old?

Yes, it is always being refreshed at a rate set by the user.

You could have a routine which copies
data after a recalculation event (which occurs when the data is updated)
and stores it onto another sheet.

This could be copy/paste with a timer?

Then you can chart these blocks of data as
separate series on a common chart.

Not sure what you mean as a novice?

Thanks for the feedback. This has been an age old problem of mine. At
least you have given me a guideline. For a novice, these problems are
horrendous, as there is virtually no material available, even in decent
Excel VBA books.

I have tried using macros before doing a copy/paste into another
worksheet, but they just seem to crash.

Could you give me an example of a routine, so then maybe I could modify
it?

Jon Peltier wrote:
Let's say you have two sheets, the "Data" sheet which keeps refreshing and
the "Archive" sheet where you will store old data.

Thanks for the feedback. There is quite a bit of stuff on the web when
one does a search for 'dynamic graphs'.

However, my proposed graph needs to be constantly updated so that I can
view trends, such as those recording stock market movements throughout
the day. The data must not be overwritten but added to.

I'll post back with my results.

Thanks again.

"Saxman" wrote in message
...

Thanks for the feedback. There is quite a bit of stuff on the web when
one does a search for 'dynamic graphs'.

However, my proposed graph needs to be constantly updated so that I can
view trends, such as those recording stock market movements throughout the
day. The data must not be overwritten but added to.

Well, at least I showed you how to archive the data. Figuring out how you
want to present it is another part of the puzzle.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Jon Peltier wrote:
Let's say you have two sheets, the "Data" sheet which keeps refreshing and
the "Archive" sheet where you will store old data.

You can set up an event procedure on the Data worksheet. Right click the
Data worksheet tab and choose View Code. In the module that pops up, choose
Worksheet from the left dropdown, and Calculate from the right. This code
appears:

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Delete the second pair of lines:

Private Sub Worksheet_Calculate()

End Sub

Almost there, but I'm really confused about the above statement.

The following code comes first?

Private Sub Worksheet_Calculate()

End Sub

What code do I delete?

Can I delete the original graph on the 'Archive' worksheet once it has
been copied to the 'Data' worksheet?

No values have been copied across in my example even though I have
checked the data and naming. I'll have to re-check or start again if
you can answer my query and see if that helps.

Do you have an example on your website?

Thanks.

Jon Peltier wrote:

Set up your chart by defining some names. Go to Insert menu Names
Define. In the dialog, enter a name for the first named range and a formula
to refer to it:

Do I do this from the Data or Archive worksheet, or does it make no
difference?

Thanks again.

Set up your chart by defining some names. Go to Insert menu Names
Define. In the dialog, enter a name for the first named range and a
formula to refer to it:

Do I do this from the Data or Archive worksheet, or does it make no
difference?

It really doesn't matter, but I thought of doing it on the Archive sheet to
help me visualize the process. Often I make dynamic charts with static data:
select data and invoke the chart wizard, and then I convert the series to
use the names. But it doesn't really matter.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"Saxman" wrote in message
...
Jon Peltier wrote:
Let's say you have two sheets, the "Data" sheet which keeps refreshing
and the "Archive" sheet where you will store old data.

You can set up an event procedure on the Data worksheet. Right click the
Data worksheet tab and choose View Code. In the module that pops up,
choose Worksheet from the left dropdown, and Calculate from the right.
This code appears:

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Delete the second pair of lines:

Private Sub Worksheet_Calculate()

End Sub

Almost there, but I'm really confused about the above statement.

The following code comes first?

Private Sub Worksheet_Calculate()

End Sub

What code do I delete?


This is irrelevant, but the VB Editor writes it when you select Worksheet:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)



End Sub




Can I delete the original graph on the 'Archive' worksheet once it has
been copied to the 'Data' worksheet?


Yes, only keep the charts you need.



No values have been copied across in my example even though I have checked
the data and naming. I'll have to re-check or start again if you can
answer my query and see if that helps.


It's hard to generalize these techniques for any particular data structure.
If you're not experienced in dynamic charts, and using names as chart source
data, it might take a few tries to get all the details right. It's a
complicated abstraction.



Do you have an example on your website?


I don't have an example of this dynamic data updating on my web site, though
there are a number of dynamic chart examples using defined names. I only got
into these DDE-type problems recently for a potential client, and don't have
too much I can share.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Jon Peltier wrote:

It really doesn't matter, but I thought of doing it on the Archive sheet to
help me visualize the process. Often I make dynamic charts with static data:
select data and invoke the chart wizard, and then I convert the series to
use the names. But it doesn't really matter.

I managed to get the graph and code running now that I awoke to a new day.

I basically didn't run the code.

Could the code run automatically with data refresh, or maybe get it to
loop until I stopped it? Would it be better to have a control button
on the 'data' worksheet and assign a macro to the code?

Thanks again.