Course Link on Wiziq - http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling
Qutting a Loop after taking users permission
VBA programs outline:
Class 2:
Copying data validation using range
for and while loop in the same program
param array
exporting file contents into a new file
creating dynamic data tables
clearing values of range selectively based on logic
Using capital IQ, factset data bases
Class 3:
Concepts of Financial Engineering
10k
Adjustment to data
Class 4:
Intro to Monte Carlo in Excel
Cho decomposition
Class 5:
Revision Project open session / Pulling data from yahoo case study (Ref: )
http://www.youtube.com/watch?v=iSlBE3CWg5Q
Areas that we will talk on:
On erro goto next should be avoided use goto in case you need to. on error goto 0 removes the effect for Future of on erro resume next
Named ranges all cases that could happen, seaching, copying, string, address not found, etc.Resetting charts Where required, ignore charts that is not there.
Correlated residuals to be used on the future values, why residual smatter in monte carlo? how to get these Correlated residuals in Monte Carlo...
Interview questions in VBA
#na from local us #na from a link?
Factset codes, sources, and adjustments
Searching #NA picked from toher values, power of NA()
Updates in term of both pages e and i
Example of array formula f ctr shift enter (matrix multiplication)
VBA notes on linking with power point
Power point advanced charting, flow charts,etc
OFFset function (example): http://support.microsoft.com/kb/324991
Some codes that we will discuss:
------------------------------------------
Option Explicit
Sub SetFormat()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
ws.Cells.Font.Name = "Arial"
End With
Next ws
End Sub
----------------
m = Range("range").Count
For j = 0 To m
If Range("range")(j).Value = "O" Then
Range("range2")(j, 0).EntireRow.Clear
Range("range")(j).Value = "O"
End If
Next j
------------------------------------
Private Sub Reset()
Dim objCht As ChartObject
For Each objCht In ActiveSheet.ChartObjects
With objCht.Chart
With .Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
End With
End With
Next objCht
End Sub
-------------------------------------------------
Sub Highlight_Cells_With_Text_or_Formulas()
'Highlights all cells with text or formulas on the active sheet
'Will remove color from cells without formulas or text
Dim r As Range
With ActiveSheet.UsedRange
.Interior.ColorIndex = xlNone
For Each r In .Cells
If r.Value <> "" Then r.Interior.ColorIndex = 3
Next
End With
End Sub
-----------------------------------
Code for playing with data validation
Private Sub Worksheet_Activate2()
Dim rng As Range, cell As Range
With Range("BL38:BL38")
Set rng = Intersect(.SpecialCells(xlCellTypeAllValidation), .SpecialCells(xlCellTypeBlanks))
End With
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
cell.Value = Sheets("Assumptions").Range("B7:B9")(1).Value
Next cell
Application.EnableEvents = True
End If
End Sub
--------------------------------
Private Sub Set_Default_values()
Dim rng As Range
Set rng = Range("Default_Values").SpecialCells(xlCellTypeAllValidation)
Range("Range_name").Value = Range("Default_Values")(1).Value
End Sub
------------------------------------------
Sub AddNewWBKs()
Dim myWB As Workbook, newWB As Workbook
Dim myWS As Worksheet
Set myWB = ThisWorkbook
Application.ScreenUpdating = False
Set myWS = myWB.Sheets("Assumptions")
Set newWB = Workbooks.Add
myWS.Range("Coverage_Data").Copy
Range("A1").PasteSpecial xlPasteValues
Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
ActiveSheet.UsedRange.EntireColumn.AutoFit
Application.DisplayAlerts = False
newWB.SaveAs Filename:="C:\Documents and Settings\shivgan\My Documents\Exported Spreadsheet.xls"
Application.DisplayAlerts = True
newWB.Close
Application.ScreenUpdating = True
End Sub
--------------------------------------------------
Public Sub Count()
Dim count_columns_selection, i, iReply As Integer
Dim range_name_string As String
For i = 1 To Range("Range_X").Rows.Count
Code for anything that may cause trouble
On Error GoTo Error_control
ContinueLoop:
Next i
Do While i < Range("Range_X").Rows.Count
Error_control:
iReply = MsgBox(Prompt:="Foudn Error?", _
Buttons:=vbYesNoCancel, Title:="BLANK???")
On Error GoTo -1
If iReply = vbYes Then
GoTo ContinueLoop
ElseIf iReply = vbNo Then
Exit Sub
Else
Exit Sub
End If
Loop
End Sub
-------------------------------------
Range("G6").GoalSeek Goal:=0, ChangingCell:=Range("G7")
End Sub
Sub reset()
Range("G7").Value = 0
End Sub
-------------------------------------------------
http://www.tresvista.com/index.php?option=com_content&view=article&id=114&Itemid=98
http://crisil.com/global-offshoring/quantitative-research.html