Category Archives: web

#Excel Super Links #156


3 ways to change an image based on a cell value

Custom Connector to import Google Sheets with OAuth2 authentication in PowerBI

Advancing intelligence, management, and security to empower the modern workplace

The Environment concept in M for Power Query and Power BI Desktop, Part 1

Generating a “Realtime” Voice Alert for the Latest Magnitude 5 or Greater Earthquake



#Excel Super Links #153


Embed your Power BI report with predefined filters

Working with implied units

The Great Football Project Rides Again! (In Power BI)

Who Has My Workbook Open? [VBA]

Highlighting Actual Words in an #Excel List Using the Hyperlink Rollover Method


#Excel Super Links #152


Bulk Download of Power BI Custom Visual Sample Files


Highlight Cells Referenced in Excel Formulas

Power Query: In Your Face(book)

Get Latest Storm Information in #Excel Using Only An Address and Storm Name


Get Latest Storm Information in #Excel Using Only An Address and Storm Name


I recently published this article about the direction and distance of a tropical system from an address.

Go back and read this article to understand the first part of the model associated with the address (entered manually in cell B1).

Originally I wanted to include the abilty to add the storm coordinates from an internet source but I could not find one, so manually entering them was necessary. However, later I stumbled across just the xml source I was looking for from the National Hurricane Center site at NOAA. It only gives the current information on a storm, so it gets “stepped on” with each new advisory. This information is for storm number 15 for the 2017 Altantic hurricane season, which hhappens to be Maria.

In order to access more than one system, I replaced the number with a defined name function called StormNum, which is then used as the URL in the WEBSERVICE function to return the desired xml document.


I also needed a list of the storms for 2017. One complication was that the NHC now gives Potential Tropical Cyclones a number like those given to all tropical lows. Then, if the PTC does not develop into a low pressure system, it will not show up in online lists, like for example, Wikipedia. However, I was able to find a site that did include PTCs and I used Power Query to get the table containing the desired information (see worksheet SysNames). I massaged the column containing the storm designations as shown in column G. The formula used for this is =IF(PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))=””,”Not Yet”,PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))). Shown below:


The source from the PQ M code is:


Now that I had the list of storms, I was able to construct the StormNum defined name formula as follows:


where Storm_Number =MATCH(DirectionCalc!$B$2,Storm_List,0). Cell B2 contains a data validation list with the storm names derived from the PQ.

Now, the FILTERXML function can extract various pieces of information from the xml doucment defined as Storm, as shown in the following figure.


For example, the storm latitude (in cell C4) is =FILTERXML(Storm,”//centerLocLatitude”).

The final formula for the storm message is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address&”, moving “&MID(FILTERXML(Storm,”//systemDirectionOfMotion”),1,FIND(“OR”,FILTERXML(Storm,”//systemDirectionOfMotion”))-1)&”at “&FILTERXML(Storm,”//systemSpeedMph”)&” mph.”

Remember, do no use this model in any corporate or commercial manner (only for personal use).

You can download the file here.


#Excel Super Links #147 – Shared by David Hager


Introducing the Timeline Storyteller custom visual for Microsoft Power BI


Filter data based on a comma separated list using CALCULATE and PATHCONTAINS

Why I STILL Teach DAX in Excel: My Lament to Microsoft

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


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&&#8221; “&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: 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&#8221;),”//lat”)

longitude= FILTERXML(WEBSERVICE(“”&ENCODEURL(address)&”&sensor=false&#8221;),”//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&”&#8221;),”//dst”)

gmtOffset = (FILTERXML(WEBSERVICE(“”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&”&#8221;),”//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.