• 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

VBA Progress Indicator

I learn how to create an VBA progress indicator from j-walk blog after surfing around and finally found the simplest one.

And now I can apply it into my portfolio optimisation model, as follows:

Sub optimise()

Dim PctDone As Single

Application.ScreenUpdating = False

nosim = Range("nosim")

For i = 1 To nosim

Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient..."

Range("simmeanresult").Cells(71 + i, 1).PasteSpecial Paste:=xlPasteValues

Count = Count + 1
PctDone = Count / nosim

With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With


Next i

Unload UserForm1

Application.StatusBar = False

SolverOk SetCell:=Range("tangency"), MaxMinVal:=1, ValueOf:=0, ByChange:=Range("proweight")
SolverAdd CellRef:=Range("proweight"), Relation:=3, FormulaText:="0"
SolverAdd CellRef:=Range("one"), Relation:=2, FormulaText:="100%"
SolverSolve UserFinish:=True

Application.ScreenUpdating = True

End Sub

Simulating Dow Jones Index in the Future

Happy New Year 2008. Wishing you a prosperous year of 2008 and years after.

I was again playing with random numbers simulation and found everlasting properous years of Dow Jones Index in the future.

Bear in mind, this is just a rough simulation played to get rid of boredness in the first day of 2008.

The process is as follows:
  1. Gather Dow Jones Index daily prices since 1-Oct-1928 and calculate the mean and standard deviation from the daily log returns.
  2. Using NORMINV(rand(), cumulative mean, cumulative stdev), simulate the daily returns.
  3. Using Price n * (1 + simulated daily return), estimate Price until n equals to 1-Jan-2100.
That's it. Easy. The index is randomly simulated everyday until year 2100. Number of simulations: 24,004.


Can you see the heavenly index level at the end? Such an amazing number, aye? That's why people should be optimistic with the future of finance and investment as glorifying God. Dow Jones Index at 31 Dec 07 is 13,264.82. Is it going to be 3,000,000 in 2100???

Download model >>>

Closer look of some scenarios

Iteration and Circular Reference

If you can't live without circular references, just turn on the iteration.

cell A + cell B = cell C
cell B = cell C - cell A

In Excel, cell B and cell C are the sources of circular references.
However, to make the calculation work as is, the iteration must be set on as the image below. (Tools>Options)

Then, we all can live with circular references.

Name a Cell

One of the important tools in the Excel is cell naming. By naming a cell, we can relate the cell name with any formulae we want to make. Furthermore, in creating macros or visual basic codes in Excel, cell naming is very helpful and important particularly in identifying the corresponding cell.

To name a cell, just go to the box 0n the top left side. Write the desired name and press enter.

For example, we want to calculate the return of share prices. With the conventional method, we use formulae =(B2/B3)-1. However, since B2 has been named as "today" and B3 as "lastyear", then the formulae becomes =(today/lastyear)-1.

This handy method is useful in a big spreadsheet when the formulae contains particular parameters that need to be referred frequently. It's also good for easily remembering and quickly referencing the formulae.

Lotto generator


This lotto generator might not explain how I was so lucky two years ago. At least, randomness may still explain how people could win a lotto.

The process of this lotto generator:

1. Running a set of Monte Carlo simulations for getting 6 numbers from 1 to 40 randomly.
2. Calculating the probability statistics from the simulations.
3. Incorporating the historical statistics since the 1st draw until the latest one.
3. Picking up the highest probability numbers.