Who's good at MS Excel?
post-2772632
Thread Starter
Post #1 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2772676
Post #2 of 26

stewtheking

Headphoneus Supremus
Joined
May 6, 2005
Messages
2,671
Reaction score
27
Joined
May 6, 2005
Posts
2,671
Likes
27
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).
 
     Share This Post       
post-2772753
Post #3 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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
 
     Share This Post       
post-2773226
Post #4 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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?
 
     Share This Post       
post-2773228
Post #5 of 26

slwiser

Headphoneus Supremus
Joined
Jun 23, 2001
Messages
6,317
Reaction score
24
Joined
Jun 23, 2001
Posts
6,317
Likes
24
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.
 
     Share This Post       
post-2773410
Post #6 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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!
 
     Share This Post       
post-2773587
Post #7 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2773729
Post #8 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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.
 
     Share This Post       
post-2774399
Post #9 of 26

Fang

100+ Head-Fier
Joined
Apr 11, 2006
Messages
125
Reaction score
10
Joined
Apr 11, 2006
Posts
125
Likes
10
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
 
     Share This Post       
post-2774505
Post #10 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2776879
Post #11 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2777426
Post #13 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2777838
Post #14 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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.
 
     Share This Post       
post-2778296
Post #15 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top