Home

 

Most of the code is written by me.
Some of it is shareware that I have amended to made more user-friendly or flexible.
You may download the PUBLIC.xls file that contains all the code. If you want to just pick
and choose the code that you specifically want for your work an easy reference is below. If you choose
to download the PUBLIC.xls, you should save it as PERSONAL.xls
in C:\Program Files\Microsoft Office\Office\XLSTART so that its always available
when you run Excel.

You can also download a useful toolbar that I use which links up to many of the
useful macros written. Make sure to save the toolbar file (Purnendu8.xlb) in C:\windows
and then open it in Excel like you would load up any spreadsheet.

Please mail me any useful comments.



Black Scholes - price, delta, vega and gamma calculated
Sub ColorMeBad()

You select a range with your mouse
then you can use this to colour the cells in that
range that are >, <, =, <>, >=, <= a value
that you specify.
e.g. colour yellow all cells in the range that are greater than
or equal to 99.

see also ColorMeGood

Created by PNath@London.edu

Sub ColorMeGood()

Undoes the effect of ColorMeBad.
You select a range with your mouse
then you can use this to reset the colour of cells in that
range (to be uncoloured) that are >, <, =, <>, >=, <= a value
that you specify.
e.g. reset to uncoloured all cells in the range that are greater than
or equal to 99.

see also ColorMeBad

Created by PNath@London.edu

Sub CreateDestroy_Spaces()

This is great for creating or destroying
cells, rows or columns at regular intervals
as many times as you want, with as many
cells/col/rows left untouched as you want.

e.g. insert 4 rows in current position
then skip next 2 rows and reinsert 4 rows
and so on, say 23 times.

e.g. delete 2 columns in current position
then skip next 3 columns and delete 2 columns
and so on, say 23 times.

Created by PNath@London.edu

Sub DeleteRowWhereBlank()
Does just as the name says

Created by PNath@London.edu

Function OLSForecast(y, x, xreqd)
returns the Ordinary Least Squares (OLS) forecast
for the linear model
y = intercept + slope * x

Created by PNath@London.edu

Sub GetListOfUniqueNames()

If you highlight a range this code produces a list of
entries in that range without duplicates.

This code is from BHanke@London.edu
supplied to me with the requirement to use Named Ranges ("NameRange", and "Target")
Changes made by PNath@London.edu
Header:=xlGuess changed to xlNo
Also changed code so that dont need to name ranges, just highlight
data and results pasted in the col on the immediate right.


Matlab code - for converting dates Excel <--> Matlab
and for converting alphanumeric to numeric
or numeric to alphabetical

Sub PasteInfo(PasteSheet, ItemToPaste, WhereToPaste, RowOffsetPaste, ColumnOffsetPaste)

PasteInfo(PasteSheet, ItemToPaste, WhereToPaste, RowOffsetPaste, ColumnOffsetPaste)

very useful in your code for pasting information on a sheet without having to
reinvent the wheel.
Use to please (yourself).

Created by Purnendu Nath (PNath@London.edu)


Sub Simulate()
to use this you need to declare the following Named Ranges on your spreadsheet
RangeWt1 (name a range with cells containing e.g. 0.1, 0.2, 0.3 .... 1.0)
RangeWt2 (name a range with cells containing e.g. 1, 2, 3, 4, 5)
Wt1 (this named CELL is what will be simulated by RangeWt1)
Wt2 (this named CELL is what will be simulated by RangeWt1)
ValueFunction (this is the named CELL output of the simulation e.g. Profit)
ValueFunctionPaste (this is where the result of the first simulation will be pasted
i.e. the value of ValueFunction when Wt1=RangeWt1=0.1; Wt2=RangeWt2=1)

Created by Purnendu Nath (PNath@London.edu)

Useful Tools - contains many useful little (sometimes even 1-liners) bits of code
that make life a lot easier. Many of these are linked to the Toolbar that you can
download and save in C:\Windows (then open it in Excel like you would load
a spreadsheet).