Solved

chart in excel 2003 with vba

Posted on 2012-03-28
8
299 Views
Last Modified: 2012-03-28
Hello,

I'm having trouble creating the a chart with this data:
A      B      C
date +  time      data
time

example:
01.01.2012 12:00      12:00            456789

There are several sets of these data. Date and time is always unique, but time is repeating each day. Data consists out of numbers.

I need to have date only on the right side (legend entries), and time (which can be used from first or second column) on the x-axis. So the resulting chart would display for each day a data line.

This problem is how to extract the date only, second problem is how to put the time entries on the X axis?

What is the best way to do this in VBA without using pivot? I'm using Excel 2003.

Thanks in advance.
0
Comment
Question by:Fernando2323
  • 4
  • 3
8 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

I'm trying (and failing) to picture the chart you want to create. If it can't be done manually, then it won't be doable with VBA either.

If you want the date in the legend, then each row will need to be a series.
If each row is a series, then there won't be a line, since it's only one data point per series.
If the time is the same value every day, then you won't see a line, either, since all data points will sit at the same spot.

Post a sample file with a few rows of sample data and let's try to create a meaningful chart manually. Once that has been worked out, it can be done with VBA, too.

But there's no use trying to write code for a chart that does not seem to be possible in the first place.

cheers, teylyn
0
 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
Hi Fernando,

I was trying to visualise what you were trying to build.  If the date and time are always unique, the 'second time' repeats each day, and the data (your figure 456789 for example) is the other data series, then the date/time + time, and the raw data would seem to be the appropriate data series for your line graph.

In Excel I mocked up a data series for a stock price per day, where the value is captured at set times per day, as follows:
Date              Time    Stock Price
01/01/13       09:00    200
01/01/13       12:00    201
01/01/13       17:00    205
02/01/13       09:00    199
02/01/13       12:00    200
02/01/13       17:00    203   et cetera...

I then copied the data range, and did a Paste Special... Transpose, so that the data came out 'pivoted':

Date                01/01/13   01/01/13   01/01/13
Time                09:00        12:00         17:00
Stock Price       200           201            205

I then selected the pivoted data range, and generated a chart based on that data.  I ended up with a line chart that had the stock price on the y-axis and the combination of date and time on the x-axis (see attached file as an example).

As I was able to do this manually using the above steps, all you need to do is repeat the process with the Macro Recorder switched on, and that will give you the 'barebones' of some VBA code you can use to achieve your goal...  providing I understood your requirements correctly!!
StockPrice.xls
0
 

Author Comment

by:Fernando2323
Comment Utility
hello armchair_scouse

thanks for your reply and efforts. Regarding the raw data this is correct. But as I wrote the chart should display a line for each day, so you can easily compare each day for example the stock price on Monday eight o'clock compared to Tuesday eight o'clock as well. The problem is, the X axis should be of limited range displaying each hour of today only once if there is data available. I will try to provide an example later.
Thanks in advance.
0
 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
Try the example above... each data series represents one day, with stock prices changing every hour between 9:00 and 17:00.  Macro behind the sheet to build the chart.  I recommend stepping through it, as when I tested it, Excel worked out exactly wheat I needed after just a few lines.
0
 

Author Comment

by:Fernando2323
Comment Utility
Great this looks very good. But still there is one problem: ( how can I transform the original data into the format in which you were using now? Can this be done with transforming or is there any additional calculation necessary?
0
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 500 total points
Comment Utility
To transform the data from my first suggestion (on StockPrice.xls) to the second suggestion (StockPriceSeries.xls) would need some VBA, Transpose on its own would not be able to do that as the second format does not repeat the time headers, it only has them once.

I've written a routine that will do the job, TransformDataSeries, in the attached file.  Not perfect - I'm writing this after waking up in the early hours and not being able to get back to sleep!!  That's my excuse anyway...   :o)
TransformGraphData.xls
0
 

Author Closing Comment

by:Fernando2323
Comment Utility
Thanks a lot! Appreciate your help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now