Tag Archives: FILTERXML

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(“http://labs.bible.org/api/?passage=”&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.


http://api.timezonedb.com/ ‘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 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.


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(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//description/text”)

Magnitude: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//mag/value”)

Time: =TEXT(FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&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.



Lookup a Bible Verse Using #Excel (w/o VBA) by David Hager

John Walkenbach was the first to create an Excel application that allowed the user to read Bible verses.


He used an approach where the entire Bible was loaded into a workbook. I decided to try using a new technique that did not require the Bible verses be part of the workbook.

But, to do this, I needed a source of the number of verses in each Bible chapter. Luckily, I found someone that had already performed that task.


After pulling in the table from this web site with Power Query and fixing a minor Text to Columns issue, I needed to unpivot the data. In order to do this, I used this Power Query technique on the table.


It required highlighting 149 columns to unpivot, which is the most I have ever heard of. Armed with a three column table with books, chapters and number of verses, I was ready to build the worksheet input cells. I created an unique list of Bible books from column A into column E by utilizing Data Advanced Filter with the unique option. I also made a list of numbers from 1 to 150 (the maximum number of chapters in any Bible book). I added a Data Validation list to cell H1 (named TheBook) using the following defined name range.

BibleBook =BibleChapterInfo!$E$2:$E$67

The next step was to create a dynamic defined name range for chaapter numbers corresponding to each book. A Data Validation list was added to cell I1 with the formula shown below.

TheChapters =INDIRECT(“N2:N”&INDEX(Chapters,MATCH(TheBook,BibleBook,0))+1)

Finally, a Data Validation list was added to cell J1 with the formula shown below.

TheVerses= INDIRECT(“N2:N”&INDIRECT(“C”&MATCH(TheBook&TheChapter,BibleChapterInfo!$A$1:$A$1198&BibleChapterInfo!$B$1:$B$1198,0))+1)

where cells H1:J1 are named TheBook, TheChapter and TheVerse respectively (see figure).


While these formulas are very useful and powerful, a discussion of how they work is outside of the focus of this article, which is to use the WEBSERVICE and FILTERXML functions to return the desired Bible verse without use of VBA code.

All of this preliminary work was done in the hope that an XML source for Bible verses could be found. And, the following site was identified as providing this.


So, the following formula does all of the heavy work in retrieving the XML output and reading the correct node.

In cell H4 =FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&TheBook&” “&TheChapter&”:”&TheVerse&”&type=xml”),”//text”

As originally designed, this formula was intended to be in cell H5 (the Bible verse cell). Well, it turned out that some of the verses returned by the web service had a topic attached, as shown below for the value for Song of Solomon 2:3.

<b>The Beloved about Her Lover:</b> Like an apple tree among the trees of the forest, so is my beloved among the young men. I delight to sit in his shade, and his fruit is sweet to my taste.

And so, the following formula removes this XLM part from the string if it is present.

In cell H5 =IF(ISERROR(FIND(“/b>”,H4)),H4,MID(H4,FIND(“/b>”,H4)+3,255))

I hope that you find the techniques demonstrated here useful.

You can download the example file here.