Category Archives: web

#Excel Super Links #108 – shared by David Hager


Calculating Travel Distance/Time in Power BI using Bing Map API

Creating custom Map Charts using shapes and VBA

WEBSERVICE and FILTERXML functions explained

Storing and Running M Code from #Excel Worksheet Cells by David Hager

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager


#Excel Super Links #104 – shared by David Hager


Get the File Name of the Current Excel Workbook

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

Excel Pop Up Selector Tool With Slicer

Use Webservice functions to Automatically Update Excel 2013 Spreadsheets with Online Data

#Excel Short and Sweet Tip #27 (Use Worksheet UDF to Add Background to #Excel Worksheet) by David Hager

#Excel Super Links #100 (Special Edition) – shared by David Hager


My Top 10 Most Underviewed Posts (IMO)

Please check out these posts on my website. There are some powerful techniques to be found here.

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

#Excel: Removing Outliers with Excel Formulas to Modify Control Limits by David Hager

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

Excel: Conditional Format Rows in List 1 that are Not in List 2 by David Hager

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

The DAX ENDSWITHX Function Equivalent

Returning the First Temperature in PowerPivot

#Excel Short and Sweet Tip #25 (Random Sound – Worksheet UDF) by David Hager



Bible Verse In Any Language Using #Excel by David Hager


I recently demonstrated what became a popular Excel technique – looking up a Bible verse using Excel’s web functions. On LinkedIn, I received a comment from Raul stating that he did not understand English, only Spanish. So, I decided to add language translating to the model. I tried to find a (free) way to do this directly from an API, but I could not. I turned instead to the translation technique I had already published which use a VBA procedure. I combined both of the methods as demonstrated in the links shown below.

You can read both of these articles to see the details of how each was constructed. Meanwhile, the key formula in cell H5 is:


where mString = IF(ISERROR(FIND(“/b>”,oString)),oString,MID(oString,FIND(“/b>”,oString)+3,255))

and oString = FILTERXML(WEBSERVICE(“”&TheBook&” “&TheChapter&”:”&TheVerse&”&type=xml”),”//text”)

and LangCode = INDEX(LanguageCodes,MATCH(Language,LanguageNames,0))

Use the dropdowns in H2:J2 to select verse and language.


Sometimes the query has to be run twice in order to work. I have not been able to solve this problem, so please run the query a 2nd time if the cell containing the verse is blank. Alternatively, if you click in the formula in H5 and press Enter, it should calculate as desired. The problem is likely due to the Excel web functions becoming confused during recalculation. I attempted to correct this by adding &T(NOW()) to the end of the formula in cell H5. It appeared to help, but I cannot guarantee it. You can also try pressing Ctrl-Alt-F9 for recalculation.

This should be useful to everyone worldwide.

You can download the file here.


#Excel Super Links #95 – shared by David Hager


An Interesting Technique For Error Handling And Enumeration Validation In VBA

Automatically Open Workbooks when Excel Starts

Look Ma…No PowerPivot!

Excel Magic Trick 1447: DAX DISTINCTCOUNT & CONCATENATEX to Count & List Stores Visited

Getting the Sunrise and Sunrise Times From an Address by David Hager


#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. ‘Requires a free API key

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 are

latitude= FILTERXML(WEBSERVICE(“”&ENCODEURL(address)&”&sensor=false”),”//lat”)

longitude= FILTERXML(WEBSERVICE(“”&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 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 The API key is stored in the defined name API_Key.

dst = FILTERXML(WEBSERVICE(“”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//dst”)

gmtOffset = (FILTERXML(WEBSERVICE(“”&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 API to reurn sunrise and sunset times for the specified location.

sunrise= TEXT(FILTERXML(WEBSERVICE(“”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunrise”),”hh:mm:ss”)

sunset= TEXT(FILTERXML(WEBSERVICE(“”&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.


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.


Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager


The key to returning valuable information to Excel is to find sources of xml data. In this example, the xml data is supplied by

The goal is to make an alert message for the latest strong earhquake occurring worldwide. The following 3 formulas return the information needed for that message.

Location: =FILTERXML(WEBSERVICE(“”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//description/text”)

Magnitude: =FILTERXML(WEBSERVICE(“”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//mag/value”)

Time: =TEXT(FILTERXML(WEBSERVICE(“”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//time/value”), “yyyy/mm/dd h:m”)&” UTC”

The WEBSERVICE function brings in the xml content from the web site and the FILTERXML function finds the desired data based on the node used in the 2nd argument.

After these formulas return the desired data for the latest earhquake with a magnitude greater than 5.0, as shown in the figure


the TEXTJOIN function can be used to construct a readable alert message. The following array formula in cell B5 affords a formatted message from the cells A1:B3.

=TEXTJOIN(IF(COLUMN(A1:B3)=1,”: “,”; “),,A1:B3)

An important feature of this formula is the use of different delimiters based on the column where the information resides.

I hope that you find these techniques useful.

You can download the example file here.