VBA For Financial Engineering & Modeling - Online 20 Hours Course

VBA For Financial Engineering & Modeling - Online 20 Hours Course

LINK: http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling

Join our VBA for financial engineering course (http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling) & get 15% discount. Ask for discount code, email - info@qcfinance.in.

Website - http://qcfinance.in/
Youtube Channel - http://www.youtube.com/user/shivbhaktajoshi

With the above registration, you will also get access to all updates and premium membership at Qcfinance.in.

Class	Topic	Duration
1	Introduction to Programming in VBA	2 Hours
2	Introduction to Quant Corporate Finance	2 Hours
3	Data Types, Ranges & Cell Arrays	2 Hours
4	Logical Operators & Control Flow	2 Hours
5	Distributions, FRM, VAR	2 Hours
6	Techniques For Handling Missing Data	2 Hours
7	Investment Banking Quant	2 Hours
8	Data Pulling Into Excel	2 Hours
9	Portfolio Optimization	2 Hours
10	Econometrics: Multiple Regression and Logistic Regression	2 Hours

Course highlights:

• Learn VBA without learning programming.
• Feel the same as you are on an IB desk.
• A demo class can be registered as per convenience.
• Doubt clearing classes available.
• Option for one on one classes available on requests.

Course plan:

1. Quant Corporate Finance (Investment Banking).
2. Quant Equity (Equity strategies and indices).
3. Time Series.
4. Yield curve/Fixed Income/ABS.
5. Binomial pricing MC/Hull white/BS/exotic options KMV.
6. Portfolio at risk.

From VBA Programming point of view (Divided into following areas):

1. Dependencies and removing arrows.
2. Picking non blank cells.
3. Selecting sheet and changing color.
4. Selection vs. entire sheet.
5. For loops, if end if loops, exiting loop, placing end, nestled for loop.
6. Importing values from another sheet, without opening.
7. Change manual to automatic formula computation.
8. Data tables with one and two variables using VBA.
9. With Command.
10. Set Command.
11. := used where?
12. Selecting cell with specific values.
13. Data tables.
14. Combining array in a single cell using delimiter.
15. type data validation, playing with ranges.
16. VBA editing of data validation.
17. playing with string to get the last value.
18. playing with axis of charts formatting.
19. Functions with many inputs.
20. On Error.
21. Option Explicit others.
22. Selective clearing arrays rows ranges by clear command.

Some key points about the course:

• Requires absolutely no knowledge of programming.
• Provide introduction about all Quantitative roles in Investment Banking.
• Highly flexible and tailored as per needs of individual (10-50 % Quant Finance & 10-50% VBA).
• Sensitization on derivative, Quant Equity corporate IB, fixed income, Monte Carlo.
• Feel the same as you while you are on the IB desk.
• Examples with real data to enhance your Financial IQ.
• Under the applicability and use on Bloomberg or Reuters websites (Introduction to tickers, RIC).
• Real recent examples and real cases which are hot in the market.
• New Interpretation, terminologies, and basic IQ for the subject covered.
• Helpful for passing FRM, CFA, BAT exams also prepares for Master level studies in Finance or career change.
• Right mix of data handling, scripting, mathematical skills.
• Contains right blend of learning and practice (Ratio 6:4).

Below are the video description of the course and the ppts used:

Addon Module on Quant Corporate Equity. This could include quant index, beta computations, different style of index, equity derivatives, importance of volume traded, value growth differences, emerging and developed markets relations, how index are made, using ric ticker, etc equity database research, etc.

Addon Module on Financial Risk: This could include Monte-Carlo, VAR, BS, Copulas pricing cdo, pricing exotic options, Modified BS models, EVT distributions, VLOOKUP, long data tables etc.

List of commands that we will use with references for self-study:
Param Array: Challenges and use of param array for dynamic number of inputs. Param array is itself added with GOTO command.

GOTO: Referring string named ranges Application. 

GOTO Reference:=abc2
'Range("qrs").Value = Selection Value
Application GOTO.
http://msdn.microsoft.com/en-us/library/office/ff839232.aspx.

The above command is used to go to a named range and select it. This is slower way to do the same.


Data Tables: Making data tables in vba, how to clear all update values, in other words enable/disable tables.

Setting default values from range to other named ranges: 2 column range and going to name using goto command and also storing value as string... clearing old values and difference between if error go to next and if error go to ext.

With Command: Use to act several attributes to an object in one go: http://msdn.microsoft.com/en-us/library/wc500chb(v=vs.80).aspx

The On Error Statement: http://www.cpearson.com/excel/errorhandling.htm.

Application.Union: Takes union of ranges.

This can be used to take union of ranges that are defined at different places.
http://www.cpearson.com/excel/BetterUnion.aspx

Call function in VBA is used to call functions based on name and parameters
http://msdn.microsoft.com/en-us/library/sxz296wz(v=vs.80).aspx

http://msdn.microsoft.com/en-us/library/office/aa204537(v=office.11).aspx
Expression will calculate the range means I think it is like replacing = with =

Sub procedure
http://msdn.microsoft.com/en-us/library/dz1z94ha(v=vs.80).aspx

.activate
http://msdn.microsoft.com/en-us/library/office/ff194565.aspx.

Function overloading in MATLAB is an interesting area, it is like defining function adhoc that will be used that time only and I think activated during the lines are used

Referencing in VBA (byRef ByVal):
http://msdn.microsoft.com/en-us/library/bb190882(office.11).aspx

Excel functions used commonly:
By val / reference
http://www.techonthenet.com/excel/formulas/index_vba.php
http://roymacleanvba.wordpress.com/2009/05/01/byref-and-byval/

.add
http://msdn.microsoft.com/en-us/library/office/aa221688(v=office.11).aspx


Other courses that you can refer to:Demo Course structure:
http://www.vtc.com/products/Microsoft-Visual-Basic-for-Applications-(VBA)-Tutorials.htm


Given below is the playlist that will have all the videos related the course:

Contact Details: shivgan@qcfinance.in, arpit@qcfinance.in (Arpit).

Contact Us for more details: info@qcfinance.in.

Excel Modeling (Advanced) for Financial Engineering (Supplement to CFA FRM prep)

Excel Modeling (Advanced) for Financial Engineering (Supplement to CFA FRM prep) course experience by Satyadhar Joshi

satyadhar_joshi@yahoo.com

Personally I think Excel is cliche, and too much mundane but one has to do what is to be done.

Below is a video about my course:

VBA for Quant Finance in 10 classes [Ver1]

Introduction: This article talks about the Excel financial modeling I took and about the important topics and interesting areas in the training. I have put in daily analysis of what I learned and pointed out anything that requires a special effort.

Tough areas to model are: Monte carlo Simulations, derivative pricing models, interest rate options, currency derivatives.

Day 1 of Excel finance training:

1. Basics of excel like relative and fixed referencing
2. Areas for excel modeling that are tricky will involve FRA and portfolio
3. Operating leverage and changes as Operating Leverage parameter changes, increasing and decreasing fixed and variable costs and looking at curves
4. Solving Liner Equation with objective and constrain functions, these are the ones of linear programming
5. Portfolio Construction and use of matrix in excel, there is 5 element matrix

Basic prerequisite for excel:

1. For typing 1 to 10 first write ist no. then in 2nd row just add 1 in that cell nd then copy b2 then using shift n down then up to that level where u wanna to reach then do ctrl +v
2. ctrl x to cut, ctrl v to paste it , use ctrl+shift+5 to make it in % terms
3. ctrl page up and crl page down to go and come back to next sheet
4. ctrl+space to select complt collumn , shift+space select row
5. use' f5' to go any cell
6. How to colour,undo and redo,f4 to lock
7. Absolute vs. relative references
8. 'IF' loop, NPV , IRR ,
9. SENSITIVE ANALYSIS. SCENARIO ANALYSIS , (ALT+W+FREEZE)?
10. TRANSPOSE, SUM PRODUCT,MMULT(SHIFT+CTRL+ENTER)
11. f2 to link down things

Day 2 on Excel Finance training:

1. Solver for Linear Modeling
2. Regression: R adjusted, return vs price for beta, regression stat, etc. these are things also important for CFA L2, FRM L1.
3. Beta calculations: Regression, formula, bottom up
4. Monte Carlo simulations
5. SIP Vs FD
6. Story of Multiple IRRs
7. Replicating Data and values

Out of these the beta is a complex issue and so is the regression thing.

Day 3 of Excel Financial Modeling:

1. Mostly about simple Monte Carlo, searching for pie, understanding the multiplication of variables
2. Then sensitivity for Duration and bond, convexity
3. Rest all was simple
4. MC was explained in a very simple way
5. Freq function
6. min max
7. percentile reading greater than 0
8. bin count
9. percent rank command
10. linking sheets ctr pgdown
11. Stat basics like kurtosis, skewness, and other parameters
12. medians and quartiles for Monte Carlo
13. regression vs correlations
14. all types of duration, derivation of formula of duration

Day 4 Financial Modeling Excel:

1. Revision of WACC optimization using solver
2. Different types of Sensitivity Analysis
3. CTR + page up to move between worksheets and referring between various worksheets
4. All functions of data analysis
5. Var and distributions
6. Is Kurtosis fat tails or peekedness 
7. Double effects of things in monte carlo
8. H-Look and V-look, the rows column match and the syntax things are conventional
9. Ratio analysis, there are around 40 ratio given in the CFA book
10. CF from parallel sheets BS and IS
11. Misc

Day 5 Excel Modeling Financial:

1. Using data table for finding scenario analysis, where we have to select 2 variables and then change the things as made in the rows and columns which is then linked to input and then things are linked downwards
2. Z spread using tool called____, and computing it based on spot rates of US treasury
3. Loan amount using
4. IRR using function
5. Du point in Excel
6. Ratio 
7. Common size
8. Crt W Z
9. Ctr W T
10. What if Analysis
11. TCF total Cf and its implication to new cash in the next years balance sheet
12. WACC and its relation to value of company
13. derivation of recievalbe days
14. last year cash + total cash flow = cash in balance sheet this year
15. salves vs turnover 
16. portfolio of 3 stocks and looking at matrix: 1*3 3by3 3*1

Matrix Multiplication

I will discuss in details all the areas especially matrix multiplication, transpose and other formula.

The most important part is to visualize the Covariance matrix, once that is done this become easy.

Summary of First 5 classes of excel finance modeling:

The experience was very good as I had a revision of all major concepts, especially Z spread and use of  Data table to predict 2 variable change in the scenario analysis. Also the matrix part requires a deeper understanding of the Matrix algebra and a revision so that I can apply for Finance.

Monte Carlo Financial Modeling in Excel:

First we need to define all equations and variable and find out the distributions of all variables, and once that is done we need to find out the future movements using Brownian motion. And then the variable which may vary as per our selected distributions. Finally we need to move ahead and draw the distribution of each outcome with its frequency or probability. So again here we need to work on Brownian motions to move into the future areas.

Week 2 Day 1 on Excel Training

1. FCFF tough questions (Requires knowledge about Cash flows, EBITA, why 1-t, different valuations models, etc)
2. Bonds
3. Portfolio of Bonds
4. Asset Liability matching
5. All 4 Valuation models were talked about but not implemented:  FCFF, Multiples, Residual income, Private Company valuations, these will be implemented

Derivation of Bond Convexity

Based on Taylor's theorem. Relation of function with first derivative and 2nd derivative.

Asset Liability Modeling

Matching is a tricky problem but requires no special skills.

FCFF

Capex and other issues, many models like Damodaran exists

Damodaran model was used to convert the Rnd, lease, EBIT normalization etc. A lot is given on his website.

Football field and making the 2 goal posts and finding out each values.

Synergy and modeling for acquisition, these were done in 2 ways, where one was fundamental and other was based on market values.

Multiple based modeling included 3 things:

p/e

p/revenue

p/sales

and finding out our company values using these multiples and which is more relevant etc.

Macro and VB

Macros are linking cell, making macros functions etc where the are just like Visual Basic. Adding graphics like check and buttons etc. Simple things were taught.

Shortcut= alt+f11

We can make the entire setting of number of digits and custom ideas for the cells.

How to use string and take it here and there.

Upcoming:

The things that I find most interesting in this area is VBA and application of VBA. Commands to manage the large data of SQL is another important concept. I think VBA will be done in the upcoming week.

Things that I want to see are hardcore Quantitative finance models.

Exam of Excel fin modeling:

The exam of the this was very rigorous and tested many concepts, let me give you an outline of the exam:

1. Breakeven point, contributions margin, effect of profit depends on the Fixed cost and variable costs and the slope of this line is important. Hence if a firm wants to change its Fixed cost or variable cost it depends on the breakeven point as it takes a qunat approach to the part about our investment on fixes or variable costs.
2. Duration and convexity of a bond and bond portfolio, this is simple, and how to hedge based on the duration, kind of same was seen in FRM exam
3. Projection of BS IS, using common size statement, and how to project future, here we had sales increasing and he had to increase the cogs, etc by the same amount, this is part of corporate finance CFA
4. portfolio management
5. alpha and beta
6. How to lever, unlever and do things to calculate beta and hence find out the cost of equity, where we have to go different industries and it was an imp thing. We need to know RFR and other things, hence beta is an important matters. 
7. But the thing that I enjoyed most is the bidder and target firm, and valuation of synergy, for this we had to calculate the reduced COGS and increased growth rate. But to incorporate all these things into accounting and Income statement is very interesting. In this term, we need to know accounting for merger acquisition, and other things.

Thus these were the main question of the test, and accuracy of 70% was required to do the same questions.

More to come:

1. IPO, merger, LBO and FPO Modeling
2. Black Scholes and binomial methods for  Derivative pricing
3. Binomial trees using Macros
4. Advanced  Bond Valuation advanced
5. Consolidation, merger
6. Taxation effects
7. Loss carry forward
8. Valuation from data of a company from scratch, which is downloading from their website, how to calculate risk free rate in real life
9. Using data from websites of NSE and data collection for modeling, linking models live to real data.
10. We have more models from Damodaran
11. Modeling for corporate control in India
12. ABS, MBS, RMBS, CLO, FSS, WBS in Excel
13. Sector Analysis: How to specialize any area for interview
14. Company Analysis: of ANY BIG ONE
15. Group presentations and making synergy 
16. Report writing skills, like how to apply news paper info to valuation

My course on VBA for Financial Engineering:

http://stockcreditfinancecfa.blogspot.com/2013/02/top-commands-of-vba-used-in-financial.html

Financial Modeling in MATLAB

MATLAB, SAS are advanced tools for modeling used in instruments of structured finance. As we have seen that Excel is good for Equity modeling, it does not hold very good when things become complex. The next Level of Excel is VB which I will be covering later.

I came across this book called financial forecasting where I did read about how I can apply all what I read in FRM L1 in MATLAB and other tools.

As depicted in my credit risk thread, I will here talk about how to simulate some of the major quant Finance aspects in MATLAB.

Written by: Shivgan  Joshi