• This service has been inactive since 2010 as my new role in TM1 consulting.
  • Please disregard any commercial offers as this website has been archived and transformed into a blog rather than a commercial website.
  • Please contact me for more info in BI and Financial Modelling consultancy.

A Noisy Market Siren

The idea is to automatically activate a noisy siren from a sound file when opening the excel file as the market index value drops to, or is below, the minimum expected value. The market index value is refreshed every 30 minutes.

Eg. the index value alert is 4,300. If the market index value is below 4,300, the noisy siren will be automatically activated.

I first set a WebQuery for the market index latest price with automatic refresh every 30 minutes. Then, apply VBA macro to call a sound file.

I modified the VBA codes taken from ExcelTip.com and J-Walk.com as follows:

Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Sub PlayWavFile(WavFileName As String, Wait As Boolean)

If Dir(WavFileName) = "" Then Exit Sub
If Wait Then
sndPlaySound WavFileName, 0
sndPlaySound WavFileName, 1
End If

End Sub
Sub PlaySoundAlert()

For i = 1 To 5

PlayWavFile "c:\WINDOWS\Media\notify.wav", True

Next i

End Sub
Function Alarm(V)

x = Range("alert")

If V <= x Then

Call PlaySoundAlert

Alarm = True
Exit Function
Alarm = False

End If

End Function