Formatting updated web data

ecokev

New Member
Joined
Jun 8, 2010
Messages
1
Hi there folks,

I am using Excel to download web data and then go on to format it and plot the information over time.

The web data is a comma separated table. I can get the data into Excel via the Web Query tool, however, it lumps all the information into one column, when it really needs to be spread over three.

I use the text-to-columns function to split the data into 3 columns, however, each time the web data is updated, all the information is put back into one column. Is there a way that I can retain the 3 column format, or perhaps a way to automate the text-to-columns function to occur each time the web data is updated.

Secondly, once I have the updated data, I would like to have it plot it against days of the month. Is there a way this can automatically happen?

If I use the following set up, it doesn't work.

1-Jun-10 | =A1
2-Jun-10 | =A1
3-Jun-10 | =A1

When the web data is updated, say in cell A1, it would change the value of the first two days. I need to keep the old value, the value that was current on that day, so that I can plot trends over time.

Thanks for any help you are able to offer me! I know it's probably pretty confusing to understand what I need, given my description, but if you need any clarification please don't hesistate :).

Kev
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,890
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Regarding your first question, using "Record Macro" you can record your steps of applying the text-to-columns routine, so now you will have the VBA code necessary to do this. Then you can just run this macro.

Depending on how your web data is upated (is it automatic? is someone doing it manually?), you may be able to put your VBA code above into an Event Procedure to automatically run this text-to-columns routine whenever the data is updated (an Event Procedure is VBA code that is automatically triggered upon some event happening, like data being updated, etc - see here for more details: http://www.cpearson.com/excel/Events.aspx). You may be able to use the Worksheet_Sheet or Worksheet_Calculate events, but it really depends on how the data is being updated.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,248
Messages
5,769,024
Members
425,509
Latest member
Menni

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top