Download 10,000 Days of Free Weather Data for Almost Any Location Worldwide

image

“And I feel, so much depends on the weather…
So is it raining in your bedroom retail?”

Example:  800 Days of Weather in New York City

image

820 Days of Weather Data from New York City, Pulled From DataMarket
(Temps in F, Precipitation in Inches)

Come on admit it.  It’s very likely that you would have a use for data like this, whether it was from a single location or for a range of locations, as long as the locations(s) were relevant to your work and the data was easy (and cheap) to get.

Good news:  I’m gonna show you how to get this same data for the location(s) you care about, for free, and make it easy for you.  Read on for the weather workbook download link and instructions.

First:  A Practical Application to Whet the Appetite

As I said in the last post, I think there’s a lot of important things to be learned if we only cross-referenced our data with other data that’s “out there.”

I happen to have access to two years of retail sales data for NYC, the same location that the weather data is from.  To disguise the sales data, I’m going to filter it down to sales of a single product, and not reveal what that product is.  Just know that it’s real, and I’m going to explore the following question:

If the weather for a particular week this year was significantly better or worse than the same week last year, does that impact sales?

Let’s take a look at what I found:

Impact of Weather on Sales of a Specific Product

RESULTS:  Weather This Year Versus Last Year (Yellow = “Better”, Blue = “Worse”),
Compared to Sales This Year Versus Last (Green = Higher, Red = Lower)

I don’t have a fancy measure yet that directly ties weather and sales together into a single correlative metric.  I’m not sure that’s even all that feasible, so we’re gonna have to eyeball it for now.

And here is what I see:  I see a band of weeks where sales were WORSE this year than last, and the weather those weeks was much BETTER this year than last.

And the strongest impact seems to be “number of snow days” – even more than temperature, a reduction in snow this year seems to correlate strongly with worse sales of this product.

Does that make sense?  I mean, when the weather is good, I would expect a typical retail location to do MORE business, especially in a pedestrian-oriented place like NYC.  And we are seeing the reverse.

Aha, but this is a product that I would expect people to need MORE of when the weather is bad, so we may in fact be onto something.  In fact this is a long-held theory of ours (and of the retailer’s), but we’ve just never been able to test it until now.

All right, let’s move on to how you can get your hands on data for your location(s).

Download the Workbook, Point it at Your Location(s)

Step 1:  Download my workbook from here.

Step 2:  Open it and find a location that interests you from this table on the first worksheet:

image

Nearly Four Thousand Cities Are Available on the First Sheet
(Pick One and Write Down the Location ID)

Step 3:  Open the PowerPivot Window, Open the connection for editing:

image

Step 4:  Replace the Location ID and Fill in your Account Key:

image

ALSO IMPORTANT:  Make sure the “Save My Account Key” Checkbox is Checked!

Don’t Have A DataMarket Account Key?

No problem, it’s easy to get and you only have to do it once, ever.

Step 1:  Go to https://datamarket.azure.com/

Step 2:  Click the Sign In button in the upper right to create your account:

image

Step 3:  Follow the Wizard, finish signing up.  (Yes it asks for payment info but you won’t be charged anything to grab the data I grabbed.)

Step 4:  Go to “My Account” and copy the account key:

image

Next:  Subscribe to the Free Version of the Weather Feed

Go to the data feed page for “WT360” and sign up for the free version:

image

I’ve Gone a Bit Crazy With This Service and Already Exhausted my Free Transactions,
But That Was NOT Easy to Do

Back in the Workbook…

Now that you’ve entered your account key, set the LocationID, subscribed to the feed, and saved the connection, you can just hit the Refresh button:

image

Send Me Your Mashups!

Or at least screenshots of them.  I’m Rob.  At a place called PowerPivotPro.  DotCom.

And let me know if you are having problems, too.

I’ll have some more details on Tuesday of things you can modify and extend, like how I added a “Snow Days” calculated column.

14 Responses to Download 10,000 Days of Free Weather Data for Almost Any Location Worldwide

  1. Anthony Babich says:

    Thanks for sharing. This might be useful out here in Arizona.

  2. Vaibhav Garg says:

    I am using XP and the UI is significantly different. I am not able to complete step 4 as I could not find a place for my account key. This is probably why the server returns error ’401′(unauthorised)

    • powerpivotpro says:

      Interesting. I will ask someone from MS to look at this.

    • powerpivotpro says:

      Hi Vaibhav – can u check your version of powerpivot for me?

      In excel on the powerpivot tab there is a settings button. Please click that and tell me what number is there. It will start with either 10. or 11.

  3. Don Tyrrell says:

    I downloaded your file, set up my Azure account, but in PowerPivot I get a completely different looking pop-up menu after clicking the Edit button on the ‘Existing Connections’ screen. Your comments section will not let me paste a pic of this screen.

  4. Derek says:

    RC0 version does not work.

  5. Andrew Hess says:

    Thanks for the information, this is fantastic!
    Question, when I analyze the data against Weather.com, the temparature data looks good but the Precipitation data is a day off… has anyone else noticed that for their location? Example: we had 2 inches of rain on 10/11/13, the datafeed has 2 inches of rain on 10/12/13.

    • powerpivotpro says:

      Interesting. I’ll ask my contacts at MS to take a look. They don’t own the data, but they of course work with the people who DO.

  6. daniel says:

    nteresting !!!, someone can tell me how to get the same info in C°.

  7. John says:

    Can’t find where to add my account key – not the same as your screenshots – looks interesting though – thanks

  8. John says:

    Went through my 100 transactions and never did get any data! Not too intuitive when using the Azure Data Market. Asks for Station IDs but I did not find a list of corresponding IDs – thanks!

Leave a Comment or Question