Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
"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 _______ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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 _______ |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
"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 _______ |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Graph
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart/graph | Excel Discussion (Misc queries) | |||
creating a dynamic graph | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic Graph not updating itself... | Excel Discussion (Misc queries) | |||
Dynamic Graph Arguement | Charts and Charting in Excel |