Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Archive for November 19th, 2013

Quote of the Day: “A man who has never gone to school may steal from a freight car; but if he has a university education, he may steal the whole railroad.”

without comments

A man who has never gone to school may steal from a freight car; but if he has a university education, he may steal the whole railroad.

Read the rest of this entry »

Written by Jacob Walker

November 19th, 2013 at 11:59 am

Posted in Quotes from Others

Petition asking for Cortez Quinn to Resign

without comments

As I have been posting about recently, Cortez Quinn has come afoul of the law.  And while some may find the soap opera slightly entertaining, it isn’t what the Twin Rivers district needs.  I believe that Cortez should make the honorable decision and resign, so that his personal issues do not entangle further with the needs of our district.  I am asking others to ask him to do the same, as the children of an already embattled district will continue to pay the price for issues that they shouldn’t, until the scandals of the district are resolved.  Cortez resigning is the right way to resolve one of them now.

Update: While a little late, I should mention that the Sacramento Bee also asked Cortez to resign.

Written by Jacob Walker

November 19th, 2013 at 9:59 am

Posted in Twin Rivers USD

Creating a Link to USPS Zip+4 Lookup in Excel

without comments

I am working on mailing a large number of organizations (which I will talk more about soon in another post), and the list I have in Excel of their mailing addresses often doesn’t include the Zip+4, which I originally thought was needed the business rate for First Class Mail. (Since my original posting, I read the “fine print” more and it seems that a standard ZIP code is sufficient for the rate, as long as I can put the appropriate bar code on the envelope.)

While I would ultimately like to make a full web scraper to automatically download the Zip+4, unfortunately Excel has limitations about how User Defined Functions (UDFs) and QueryTable can work with each other (specifically, I want to create a temporary sheet to download a URL into, to then get a particular cell, and UDFs just can’t do that.)

So as a temporary workaround until I can program something either in Python or write some VBA code that isn’t a UDF, I have created a formula that creates a link to the USPS Zip+4 Lookup webpage.  For those who are interested, here is the formula:

=HYPERLINK(“https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=”&SUBSTITUTE([@ADDR],” “,”+”)&”&address2=&city=”&SUBSTITUTE([@CITY],” “,”+”)&”&state=”&[@STABBR]&”&urbanCode=&postalCode=&zip=”,”USPS Lookup”)

I should note that this seems to handle having address1 and address2 combined in the same field of ADDR.  You could modify the formula a little if you have separate fields in your spreadsheet for address1 and address2.

Update: It looks like if you do this too many times you get the following message:

To learn about integrating the free Postal Service® Address and Tracking API’s into your application, please visit www.usps.com/webtools.

I will investigate this more, and see how the API might work with Excel.

Written by Jacob Walker

November 19th, 2013 at 5:39 am