#Excel: Getting the Sunrise and Sunrise Times From an Address by David Hager

A while back I worked for quite some time on returning the sunrise and sunset from an address using VBA. I was not successful, so I shelved that project. But, after publishing several popular articles using Excel’s web functions, I decided to revisit this project using those functions. As always, implementation of this technique requires the appropriate xml data sources and the corresponding return values. I could not locate a single xml source to do this, so I finally found 3 xml sources that, if combined, would theoretically provide me with the desired result. Here are the 3 API sources.

http://maps.googleapis.com/maps/api

http://api.timezonedb.com/ ‘Requires a free API key

http://new.earthtools.org/

The 1st API takes a detailed address and returns latitude and longitude for that location. The 2nd API takes latitude and longitude as input and returns a daylight savings time (dst) value and a GMT offset (gmtOffset) value. The 3rd API takes latitude, longitude, dst, gmtOffset and the current day and returns the sunrise and sunset times. For the demonstration, address is a defined name formula.

address =”4163 W. Sparrow St., Orange, Tx.”

However, a named cell could also be used to contain the address.

The two formulas needed to return latitude and longitude from googleapis.com are

latitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false”),”//lat”)

longitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false”),”//lng”)

Note that the Excel ENCODEURL function is needed to convert the address to a form compatible with the API.

The latitude and longitude coordinates are then used as parameters in the timezonedb.com API to return values for daylight savings time and GMT offset. An API key is required by the web site owner, but you can register and get a free one at https://timezonedb.com/register. The API key is stored in the defined name API_Key.

dst = FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//dst”)

gmtOffset = (FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//gmtOffset”)/3600)-1

Finally, all of the parameters from the previous 2 APIs plus the TODAY function are used in the earthtools.org API to reurn sunrise and sunset times for the specified location.

sunrise= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunrise”),”hh:mm:ss”)

sunset= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunset”),”hh:mm:ss”)

Note that the date parameters can be changed if the day to get results for is not today.

The results of the formulas used in this technique can be seen in this figure.

SunriseSunset1

The formulas have been converted to values in the model due to my inability to share my API key, but you add back in what you want to see with the formulas described in this article (and in defined names).

You can download the file here.

SunriseSunsetInfo

Advertisements

2 thoughts on “#Excel: Getting the Sunrise and Sunrise Times From an Address by David Hager

  1. Pingback: #Excel Super Links #95 – shared by David Hager | Excel For You

  2. Pingback: #Excel Super Links #100 (Special Edition) – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s