[Pepsione1]

If I want to plot a set of data points on a graph over a period of time. The data source is in a separate workbook. New data is append to this workbook over this period of time.

How do I create a chart that will compress it's scale as more data points are displayed on the screen? So far I can only set it to plot 100 possible points on a fixed scale.

 

[stewtheking]

While I can't help you with the excel problem at hand, I will say that generally excel is a shockingly inadequate graph-drawing package. For anything useful, you're far better off with something like Graphpad Prism, which although is expensive, makes light work of very complicated stuff.

And for instance, graphs created in prism automatically update their axes as new data is added. However, prism doesn;t do many of the functions that I presums you are using in excel (calculating values within a cell for example).

 

[Gord SW Ont]

There's so many ways to do things in Excel and it's difficult to say which is the best without knowing all the details but here's a way to add data which automatically updates a chart.

You need to automatically enter a formula in a couple of ranges and then select the data series on the chart and enter the formula as per the notes.

Not sure this will meet your needs but at least it's a start which you may be able to adapt to meet your needs??? Good luck.

http://www3.sympatico.ca/gord.lange/..._to_chart1.xls

 

[Pepsione1]

That's a very good example Gord. However, the data file is built & exported by a independent program and I don't have any control on how the file is created.

Is there anyway to do the exact same thing you showed?

 

[slwiser]

Excel is the wrong tool for this project. There are many much better tools. But it could be done in Excel, you would have to throw away the way you always use it and work with the VB scripting layer which is the macro language. You would almost have to go back into the DOS world with looping to use this I would think or use some type of timer to trigger updating.

 

[Gord SW Ont]

Quote:

Pepsione1 wrote: That's a very good example Gord. However, the data file is built & exported by a independent program and I don't have any control on how the file is created. Is there anyway to do the exact same thing you showed?

This is the problem with trying to solve something like this (computer/computer app/etc) over e-mail.

I'm not sure "exactly" what currently exists (and in what format) or exactly what you want done, etc. When playing with databases that's pretty important.

As the other posters said Excel may not be the ideal solution or even any solution (if one of your requirements requires functionality that Excel doesn't have, such as, more than 64,000 rows, etc or needs Visual Basic programming especially if decisons/branching/looping/etc are required).

That said it is amazing what can be done in Excel. In your example, perhaps you could try the menu item "Data - Get External Data - Import Text File" or the query option in the same menu. Just for fun, I did this by saving some data in a text file format and used this import wizard to insert the data at the end of the column in the spreadsheet I attached in my last post. It pasted in and automatically updated the chart just as if you copied and pasted the data manually. Not sure if this is helpful though.

There are likely other possibilities but as I said above without having all the information it is difficult (if not impossible) to come up with a solution.

Not sure how many data points you want to plot but obviously your graph would get pretty crowded very quickly. There are ways to just plot the last "n" number of data points though.

Sorry I can't be more helpful ... good luck!

 

[Pepsione1]

I will give you a complete run down of what I am trying to do.

I have a program that do some data logging. It then creates a file full of recorded data points in respective columns. The program can append to this file as time passes and values are added.

I want to be able to plot these points as it gets appended and updated by the program. Meanwhile keep changing the scale of the time axis as needed. Therefore the x-axis isn't fully compressed when the chart has only a few data points.

 

[Gord SW Ont]

Quote:

Pepsione1 wrote: I will give you a complete run down of what I am trying to do. I have a program that do some data logging. It then creates a file full of recorded data points in respective columns. The program can append to this file as time passes and values are added. I want to be able to plot these points as it gets appended and updated by the program. Meanwhile keep changing the scale of the time axis as needed. Therefore the x-axis isn't fully compressed when the chart has only a few data points.

Not sure if I have the time but if you have a small subset of data (say a dozen points) in the actual output file format that I can look at it I'll see if I can suggest something??? If it's a proprietary format there may be nothing I can do but if it's in a more common database format (ie csv, etc) there is a chance of coming up with a possible solution.

 

[Fang]

Sorry to hijack your thread.... but does anyone know how to import data like exchange rates from the internet to excel and include the new data into a current graph?

Thanks

 

[Pepsione1]

Hey Gord, I got it to do what I want it to do this morning.

I used your method and just referenced everything to my data file. I can see instant scaling and changes on the graph as the data file is modified.

Thanks for everything.

 

[Pepsione1]

I played around with it a little more and I am able to connect to the SQL server to import a database. This is great and the data is easier to access and manipulate.

 

[Gord SW Ont]

Great stuff!

Glad you were able to work it out.

 

[Pepsione1]

Well what do you know. Getting the data in Excel from a SQL server isn't enough. I need to use scripting in another program to do access the database.

I don't have the slightest clue how the DB is built and exported to the server so how do I go about accessing what I need? I want to be able to see the DB visually like you would in windows explorer. Then I know what I need to retrieve from the DB. Got any clue Gord?

Damn it, just when you think you have it figured out they throw something else at you.

 

[Gord SW Ont]

I'm not sure I understand what you mean? If you have the data in Excel then you should know how the database is built??? However, if you are trying to get the data into Excel then as I mentioned before the menu item "Data - Get External Data" is one option. Playing with either the "New Data Query" and "Run Saved Query" features might help. These should allow access to such database sources as dBase files, Excel files, MS Access database files, etc. (although you may have to load these from your install disk if not already there).

Not being sure what your problem is I'm guessing that you have a backend SQL source and as an intermediate step you could also use Microsoft Access to download the data ... menu "File - Get External Data - Input or Link Tables" may do it??? Then either chart it in Access which has minimal charting capability or import it into Excel (using the above process) which has better charting functiuonality.

The import/export wizards within Excel and Access are pretty good at moving data around and converting it into usable formats.

That's all I can suggest at the moment.

 

[Pepsione1]

Now we are toying with the idea of not using Excel at all. No charting, or external data retrieval in excel. Just use scripting in an app to bring data in from the SQL server.

So how do I go about retrieving data from a DB without knowing how the DB is structured. I have never programmed with SQL before so maybe I am asking for something unnecessary.