Monthly Archives: September 2017

#Excel Super Links #155

 

Forms for Excel, new experience for Excel survey in Office 365

https://techcommunity.microsoft.com/t5/Microsoft-Forms-Blog/Forms-for-Excel-new-experience-for-Excel-survey-in-Office-365/ba-p/109195

Highlight a Specific Data Point using Power BI

http://prathy.com/2017/03/27/

visualizing change via slopegraph

http://www.storytellingwithdata.com/blog/2017/9/19/visualizing-change

Reverse Geocoding Using Google API In VB

https://chandoo.org/forum/threads/reverse-geocoding-using-google-api-in-vb.18005/

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

https://dhexcel1.wordpress.com/2017/09/22/generating-a-realtime-voice-alert-for-the-latest-magnitude-5-or-greater-earthquake/

 

Advertisements

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

 

I recently published an article about getting information on the latest earthquake of magnitude 5 or greater.

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

Please read this article to see how the core model was constructed.

One problem with this model is that since Excel’s web functions are non-volatile, a formula containing those functions must be recalculated by reentering the formula. I decided that an easier way was needed to trigger an update. I also recently published an article which utilized the hyperlink rollover technique.

https://dhexcel1.wordpress.com/2017/09/15/highlighting-words-in-an-excel-list-using-the-hyperlink-rollover-method/

I figured that this might be a good way to trigger a recalculation. And, since I was going to use a VBA function to be called from the hyperlink formula, I thought that adding audio functionality would be useful as well. Here is the hyperlink rollover formula used (in cell D5, named Recalculate). Since a rollover is required, the technique is not truly realtime.

=IFERROR(HYPERLINK(EarthQuakeAlert(),”Recalculate”),”Recalculate”)

And, here is the VBA function called by “rolling over” (passing the cursor over) that cell.

Function EarthQuakeAlert(Optional Person As String = “Him”, _

Optional Rate As Long = 1, Optional Volume As Long = 80)

Static xlApp As New Excel.Application

Dim Voc As SpeechLib.SpVoice

Set Voc = New SpVoice

Dim sAddress As String

‘Application.Volatile True

xlApp.CalculateFull

If Range(“d1”).Value = Range(“b3”).Value Then

MsgBox “No new earthquake > 5.0”

Else

With Voc

If Person = “Him” Then

Set .voice = .GetVoices.Item(0) ‘male

ElseIf Person = “Her” Then

Set .voice = .GetVoices.Item(1) ‘female

Else

End If

.Rate = Rate

.Volume = Volume

.Speak “New Earthquake Alert! ” & Range(“b5”).Value

End With

Range(“d1”).Value = Range(“b3”).Value

End If

EarthQuakeAlert = “Recalculate”

Set xlApp = Nothing

End Function

In order to use SpeechLib.SpVoice in the code, the correct reference (from Tools, References) must be added to the VBE as shown in the following figure.

AudioEarthquakeAlert1

In this figure is a picture of the earthquake model.

AudioEarthquakeAlert2

I hope that you find this useful. You can download the file here.

earthquake_audio­_alert

#Excel Super Links #154

 

Excel Magic Trick 1465: Conditional Formatting Holiday Dates with Red Fill & Word “Holiday”

https://www.youtube.com/watch?v=DkchPSWIwHE

Text Files in VBA

http://www.excelgaard.dk/Lib/Text%20Files/

Find level of Directories / Find String in Power BI

http://prathy.com/2017/02/1801/

Connecting to APIs with Power BI (Power Query)–Part 1 of 2

https://www.poweredsolutions.co/2017/01/24/connecting-to-apis-with-power-bi-power-query-part-1-of-2/

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

https://dhexcel1.wordpress.com/2017/09/15/highlighting-words-in-an-excel-list-using-the-hyperlink-rollover-method/

 

#Excel Super Links #153

 

Embed your Power BI report with predefined filters

https://www.kasperonbi.com/embed-your-power-bi-report-with-predefined-filters/

Working with implied units

https://newtonexcelbach.wordpress.com/2017/09/19/working-with-implied-units/

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

https://powerpivotpro.com/2017/09/the-great-football-project-rides-again-in-power-bi/

Who Has My Workbook Open? [VBA]

https://colinlegg.wordpress.com/2016/07/31/who-has-my-workbook-open-vba/

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

https://dhexcel1.wordpress.com/2017/09/15/highlighting-words-in-an-excel-list-using-the-hyperlink-rollover-method/

 

#Excel Super Links #152

 

Bulk Download of Power BI Custom Visual Sample Files

https://datachant.com/2017/09/15/bulk-download-power-bi-custom-visual-sample-files/

Using KEEPFILTERS in DAX

http://www.sqlbi.com/articles/using-keepfilters-in-dax/

Highlight Cells Referenced in Excel Formulas

https://www.myonlinetraininghub.com/highlight-cells-referenced-excel-formulas

Power Query: In Your Face(book)

https://www.sumproduct.com/blog/article/power-query-in-your-facebook

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

https://dhexcel1.wordpress.com/2017/09/18/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.

https://dhexcel1.wordpress.com/2017/09/12/using-excel-to-find-how-far-the-storm-is-from-your-location/

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.

http://www.nhc.noaa.gov/storm_graphics/AT15/atcf-al152017.xml

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.

=WEBSERVICE(“http://www.nhc.noaa.gov/storm_graphics/AT”&StormNum&”/atcf-al”&StormNum&”2017.xml”)

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:

 StormUpdate2

The source from the PQ M code is:

=Web.Page(Web.Contents(“http://www.theweatherguys.com/index.php?config=&forecast=tropsystems&alt=tropallsystems”))

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

=IF(Storm_Number<10,”0″&Storm_Number,Storm_Number)

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.

 StormUpdate1

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.

DirectionFromStorm_AutoLookup