This morning I emailed Dr. Baker, the President of the International Educational Data Mining Society, the following open letter that encourages the society to rename itself to the International Educational Data Science Society, which I hope will spark dialogue within the educational data science/mining community.
Month: November 2013
I am overall a fan of Amazon and Kindle Edition books. But I don’t own a Kindle and I don’t like installing third party software on my PC, so I often use the Amazon Cloud Reader. But, there are two annoying problems that the Cloud Reader has without knowing the “secret” solutions: Linking directly to a book and copying text to the clipboard. This morning I figured out how to do both of these things.
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.