Graphing data from web over time

July 11, 2008 9:57 PM

I have created an excel spreadsheet that pulls data from the web, it works great. The only problem is that I can't tell if the data has changed over time. The website that I am pulling from doesn't track changes over time but it would be helpful for me. Is there a way to graph the data over time?
posted by jestonb to technology (5 comments total)

(Disclaimer: I know very little about excel) Could you have the spreadsheet pull the data to a new sheet each time and then graph the differences between sheet?
posted by jjb at 10:18 PM on July 11, 2008

Do you have this sheet anywhere where I can see it? I'd be able to help more if I could play with it while I think.

Off the top of my head, I think you would need some sort of adding a column/row/sheet solution.
posted by theichibun at 11:03 PM on July 11, 2008

Seconding thechibun's request for more info because the solution depends on a) how much data you're talking about; b) whether you have any involvement in the spreadsheet as it pulls data from the web or whether that's automated, and c) whether the data's value graphed over time lies, in part, in seeing whether it hasn't changed, meaning that you would want regular x axis intervals.

As thechibun suggested, you can add a column, row or sheet. I would entitle that new chunk in some systematic way that incorporated the date it was created. If you need the regular time intervals, the simplest way would be to pre-create the columns (or whatever) with the correct dates (in headers) and include a matching function so that the data winds up in the right place. Alternatively, the data could be imported to the same place every time but would only get housed in the column that matches it... accomplished fairly simply by a routine on closing that wiped out the matching function when the corresponding storage cells had been filled.

Regardless, the data range representing the inputs into your graph will need to be built to automatecally accommodate the new area, but that's pretty simple.

Building on thechibun's idea, if your file requires more than the addition of a column, row or sheet, you can also write a routine where the original excel file automatically saves itself under a new name that incorporates the then-current date.
posted by carmicha at 8:10 AM on July 12, 2008

Thanks, here is the link to the website that I am pulling data from:
https://secure.sos.state.or.us/eim/publicAccountSummary.do?filerId=931
I have the excel sheet set up pull the entire account summery. As you can see the website does not track when data is updated or changes it just shows a static amount.
posted by jestonb at 9:01 PM on July 12, 2008

Your web query will be attached to a particular cell or cells in your existing sheet, so it's probably best not to fiddle with that.

What you want to do is copy the column of figures, and probably the extract date, into a column on another sheet, building up a table of details on the other sheet without stuffing about with your query. Then base your chart off that table. The copy/paste (special, values) from the query page to the data page could be manual, or you could script it with not-too-much vba.
posted by pompomtom at 4:25 PM on July 24, 2008