Need Excel Macro To Retain Data Polled From Web Page

[PeterVadala]

I'm the director of a non-profit organization that operates a Webcast. We play music, and the Performing Rights Organizations (PROs) want to know exactly which IP addresses are tuned in, every minute of every day.

Our very inexpensive Webstreaming company does not provide us with a spreadsheet with this data. However, it does provide a single Web page which Excel is able to poll, and which we need to poll, every minute of every day.

We use the "Get External Data"/"From Web" option in Microsoft Excel to constantly refresh the workbook's worksheet, "Raw_Data."

So every minute of the day, we have succeded in having Excel automatically update a table that looks like this:

<TABLE style="WIDTH: 223pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=297><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=56>Country </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=72>Listener IP</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=73>User agent</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=96>Listening time</TD></TR></TBODY></TABLE>
United States XXX.XXX.X.X
United States XXX.XXX.X.X.

We only really care about the first two columns, at this point; the others are blank above.

We have a maximum of 1000 IP address-listeners, so we would have a maximum of 1000 rows of data. (For the sake of easy data storage, it may be of benefit, in the interest of simplicity and size, to reduce "United States" to 1 and all other countries to "0," because all we really need to know is whether the IP is in the United States or if it isn't -- but that's a side matter)

Because there are a maximum of 1000 listeners, including the first row as a heading, there are a total of 1001 possible rows of IP addresses. If only 700 people are listening, rows 702-1001 are left blank.

You might say, well, that's fine and dandy -- Excel is regularly polling the Web Page, and the data is consistently updated.

So what's our problem? Well, the problem is that every minute, when Excel goes to update this data from the Web, it overwrites all that old data. So Every single minute, when Excel goes to find out the new list of IP addresses tuned in to our Webcast, it replaces all the information in the column, all the 1001-or-less rows of the column, rows of IP addresses -- with the new data.

And that is not acceptable, since we need to retain all of that data.

Let's forget about the country column for now, and we'll deal with that later (i.e. the United States column). In the interest of simplicity, let's just deal with that one column of critical IP addresses. It changes automatically every single minute of the day.

What I need this macro to do is:

WHAT WE NEED THE MACRO TO DO:
1) Trigger itself/run EITHER: (a) (preferably) Every minute of the day, once a minute, at 60-second intervals OR (b) whenever data is changed in Excel
2) Copy all data in Column B, Rows 1-1001.
3) Paste all data in the NEXT AVAILABLE COLUMN of another worksheet, (IP_Log) For instance, at 1:01p.m., Excel needs to paste the current data from the Web data worksheet (Raw_Data) into column A, rows 1-1001, of the destination worksheet (IP_LOG). At 1:02p.m., Excel needs to be smart enough to paste the current data from the Web data worksheet (Raw_Data) into column B of the destination worksheet (IP_LOG), since the whole point here is to not overwrite data. In other words, it cannot put data from 1:02p.m. in the same column as the data from 1:01p.m. If it's 1:03p.m., the data needs to be posted in column C. At 1:04p.m., it needs to post the data in column D. So I think, with my limited knowledge of macros, that what we want this thing to do, is check to see if a column has any data in it, and if it does, proceed to the next empty column, and post there.

A few notes: If this would better be accomplished by paste-special-ing and converting our columns in the source worksheet to rows in the destination worksheet, that is acceptable.

There needs to be a mechanism in place for taking care of beginning a new worksheet when Excel gets more full of data than it can handle.

We have Microsoft Access available to us, so if you'd rather write this as an Access Macro -- if it would be better at handling these massive amounts of data, please let me know, and you can write this for Access

I have extremely limited knowledge of macros. I'm not a programmer in the least -- I just direct the non-profit foundation. We have the cash to go forward with this Webcast, and the music, and the human resources -- but we really need a solution to track IP addresses, and this would be immensely helpful. Our mission is to present a positive music media choice for the greater Boston area.

If you'd like us to email you a copy of the Excel sheet so you can work off of it, I'd be happy to send it to you. Let me know if you can help, and I'll figure out a way of getting my email to you.

Thanks for your help.

Peter

 

[Hermanito]

I think it is unreasonable of the PRO's to expect a minute by minute report, hell, I think it is unreasonable to demand anything for a simple webcast from a non-profit for at most 1000 listeners...

But since that is a whole other discussion, I'll try to answer a bit more to the point:
- a macro for what you want is not too complicated, but it will gather lots of data very quickly, 2.88 million values every day (worst case, assuming 1000 listeners every minute of the day). Data which will be, imho, useless without some good post-processing, because many IP's will appear multiple times, at least I'm guessing people listen in longer than 1 minute. And do you really believe the PRO's are going to do the postprocessing? I think they will just check how many listeners you logged for each minute and count the total, and that is what is going onto their bill...
- technically, copying 2000 values every minute is not a problem, not to another worksheet, not to Access. But you can run into problems if your pc cannot keep up, for any reason at all, for instance in saving new workbooks. With the amount of values every day, it is probably better not to opt for Access, you would get into problems after a few days. Access is not made to handle records into the millions, and in this case you can have almost 15 million records in just 10 days.
- Copying the values to new columns each time in a separate worksheet would be the way to go here, but I'm guessing you use Excel 2003, so you are limited to 255 columns, which means at most 3 hours can be stored in one worksheet, unless you add more data in rows below row 1000, but then the amount of data might impact save times to unacceptable delays above one minute...