Learn how to Work Smarter

Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Friday, March 12, 2010

Keeping real-time river and precipitation data, er, current

Been dragging my heels too long on my "data normalization" piece -- was sharing hydrology data with another staff today and figured this needed written up.

One of our projects is 1) adjacent to a tidally-influenced body of water; 2) has a RCRA cap; and 3) a bunch of transducer-outfitted wells each collecting 48 records/day to prove or disprove hydraulic connection with said river.  So we track environmental parameters including daily river fluctuations and local rainfall.

Enter the Internet and web scraping.

There are a ton of different ways of pulling data from the 'Net but, frankly, the easiest to validate -- prove is working to those who ask -- is the HTTP interface (type of text handled by a browser - a webpage).  I found Excel is a fine way of scraping raw delimited text, then link that to Access and append new records where they belong.  (As municipalities improve XML interfaces I expect to go that direction -- which should make this a little more efficient!)

First -- get the river data.  The USGS provides 30-minute increment stage, flow and discharge data for the river through their website.  They have a whole page on automated data retrieval -- but with the usual caveats I worked the quick-n-dirty.


Second -- collecting precipitation data.  Our City (that works!) has a nice network of automated bucket gages hosted by the USGS.  Pulling that into Excel looks like this (right):

The pink-ish cells are the "scraped" HTML -- the cells to the right contain a little text handling, stuff like:

  • date_PST: 
    • =LEFT(A15, 11)
  • inch_precip: 
    • =IF(ISNUMBER(VALUE(B15)), VALUE(B15)*0.01, "")
  • bad_data_flag: 
    • =IF(ISNUMBER(VALUE(B15)), "FALSE", "TRUE")

Next, I used linked tables in Access to pull this into a "staged" area -- which I think is important when you're relying on outside data -- you can control and manipulate the actual data table by using SQL queries to pull from the staged data.

Here are the linked tables (Access shows them in little Excel icons), named ltblPrecip_top60 and ltblRiverGageUpdate:

Then the queries to pull data into tblPrecipitationGage and tblRiverGage look like this:
INSERT INTO tblPrecipitationGage ( LOC_ID, FILEORIG, DTT_PST, INCH_PRECIP, DATA_FLG, UpdateUser, UpdateTime, UpdateDescription )
SELECT "BES Station Number 193" AS LOC_ID, "http://or.water.usgs.gov/non-usgs/bes/astor.rain" AS FILE_ORIG, DateValue([date_measure_PST]) AS RainfallDatePST, ltblPrecip_top60.inches_rainfall, ltblPrecip_top60.bad_data_flag, fOSUserName() AS UpdateUser, Now() AS UpdateTime, "qryAppendNewPrecipitation" AS UpdateDescription
FROM ltblPrecip_top60
WHERE (((DateValue([date_measure_PST]))>(SELECT Max(tblPrecipitationGage.DTT_PST)  FROM tblPrecipitationGage) And (DateValue([date_measure_PST]))<(DateValue(Now()))));

And...:
INSERT INTO tblRiverGage ( LOC_ID, FILEORIG, LoggingDTT, TimeDatum, ELEV_NAVD88, Velocity, Discharge, Data_Flag, UpdateUser, UpdateTime, UpdateDescription )
SELECT ltblRiverGageUpdate.LOC_ID, "http://waterdata.usgs.gov/nwis/uv?14211720" AS FILE_ORIG, ltblRiverGageUpdate.loggingdtt, ltblRiverGageUpdate.TimeDatum, ltblRiverGageUpdate.Elevation_NAVD88, ltblRiverGageUpdate.Velocity, ltblRiverGageUpdate.Discharge, ltblRiverGageUpdate.bad_data_flag, fOSUserName() AS UpdateUser, Now() AS UpdateTime, "qryAppendNewWillametteRiver" AS UpdateDescription
FROM ltblRiverGageUpdate
WHERE (((ltblRiverGageUpdate.LOC_ID)=14211720) AND ((ltblRiverGageUpdate.loggingdtt)>(SELECT Max(tblRiverGage.LoggingDTT)  FROM tblRiverGage) And (ltblRiverGageUpdate.loggingdtt)<(DateValue(Now()))))
ORDER BY ltblRiverGageUpdate.loggingdtt;
Okay, those look a little hairy -- but they aren't that bad.  Really.  :-)

Gonna have to wait until tomorrow for a reasonable explanation of those -- this is a long post, and I'm out of time.  (The dog is home with her legs crossed!)

Thanks for checking this out.

Tuesday, December 15, 2009

User-defined functions

Been working on a large worksheet today, and found it necessary to write a couple functions to call when Excel's builtin functionality fell short.  Cool stuff!

http://msdn.microsoft.com/en-us/library/ms499792.aspx

Tuesday, December 8, 2009

Spreadsheets are not databases.

Normalization -- what is it, and why is it important?

What:
A series of rules -- that's what database normalization boils down to.  I have had a terrible time trying to distill the "why" of database normalization, largely because I get all excited about the "what!"  So, in more concise terms than I can muster, enter the Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization

Why:
Databases start small, often with an Excel spreadsheet.  But -- this is important --  they're designed to get big.  Because many of us manage datasets with more than 60,000 records -- or will at some point.

How do these rules help big data sets?  Two ways:
  • managing data "types"
  • enforcing rules within a row, or record
  • enforcing rules between rows and
A data type is a date, a currency amount, etc; think of Excel's "format" function and you get the idea.  This rule persists throughout the column

Three levels of normalization:
http://www.troubleshooters.com/littstip/ltnorm.html

More depth on our target level of normalization:
http://en.wikipedia.org/wiki/Third_normal_form

Important translations:
Column => Field
Row => Record
So that's where we'll begin.  I'm going to go hop on a plane in a minute.

Wednesday, November 25, 2009

Creating Charts in Access 2007

A coworker Ted from GSI used the Access 2000 Chart OCE tool for some nice-looking bar charts in a legacy database; when I looked for the tool in 2007 it was less-than-obvious. However, after playing around with the toolsets for half a day I discovered that it's basically the same graphing engine available in Excel -- just with a terrible interface.


This article summarizes some of the shortcomings (one needs to tweak their own query regardless of how the original is built in the interface), and does a better job than I would've showing you where to start:
http://www.vb123.com.au/toolbox/09_access/access2007charting.htm

To see this in action at GSI check out the GWMA db > hydrograph > frmHydrographByDate > select your well and date range, load it, and click the "Chart" button at the bottom of the page (launches frmHydrographChart).
 


Shortcoming -- since I rely on a scalar query to pick the most recent measured elevation for each well, Jet cannot create a crosstab of this data so it's difficult to compare multiple wells in this interface without exporting to Excel.

Thursday, November 19, 2009

Automated PDF creation

Recently had a set of Excel charts (hydrographs) to print to one PDF, found it more challenging than I thought.

I had exported a water elevation query (~8,500 records) to Excel that looked like this:



...then macro'd new charts based on changes in row data, and - like magic - created 60+ line scatterplots.  Whee!  Thinking back, I'd do this differently next time.  But "it's consulting" so it's time to keep the head down and finish the job...

This is where I learned that selecting simply trying to print the entire workbook to PDF creates, unfortunately, as separate PDFs as you had charts!  (Not to mention the data.)  Sigh.

Enter google and this article: Using VBA for Excel and PDFCreator to print multiple pages to ONE pdf.
http://www.excelguru.ca/node/21

Sunday, July 12, 2009

Vision.

Still basically trundling through VBA in Access and Excel for my data management, with a little toying around SQL Server.

However, I'm a little seduced by this (not so!) new way of thinking about relational data -- less tabular, more XML:
http://www.codeproject.com/KB/XML/XMLFoundation.aspx

Purpose.

Began this blog as a personal reference: a place to post links, troubleshooting algorithms, pearls of insight that can be a challenge to track.

Seems there are pretty evident patterns in environmental data management (EDM), and the several firms I've worked with over the last seven years generally handle things the same way: as long as it's billable there's no reason to leverage repeated 'business' logic. I'm trying to modularize these repeated tasks.

Hope this may become a collaborative, probably local, tool when it contains enough value.
Home | My Schedule (Free/Busy) | Professional CV | Learn how to Work Smarter

A Little More Background

Friends & Followers