Loading... New & Updates

:: Oct 2024
Write a WS range to VBA static array - based on an Excel range containing a list of ASX non-trading days, a VBA procedure converts the range to a text version of a Static VBA array
:: Nov 2023
Using the WS FILTER function with Excel Tables - an xlsx file link [315 KB]
:: Sep 2023
Data validation w/ multiple lists - now titled Data validation w/ multiple lists plus Stock DataType - includes Cell, and Image version.
Exchange non trading days - ASX & NASDAQ - now includes 2023 non trading days.
:: Aug 2023
Black-Scholes: Excel and VBA - Black-Scholes option pricing. Grid version including LAMBDA function, and VBA code. Update adds LAMBDA version.
Statistical functions - worksheet - corrections applied.
:: Nov 2022
DemoIBCR - a VBA sub demonstration - InputBox method was CurrentRegion as Default value
StockReturn - a simple VBA function - Log, Discrete, Option method, #Value error
:: Oct 2022
VBA - Days Count - Actual, European and NASD methods using mathematical operators, logic statements, and code control procedures
VBA - xlfDateToSerialNumber - ISO yyyymmdd date to serial date using mathematical operators, logic statements, and code control procedures
:: Jul 2022
Exchange non trading days - ASX & NASDAQ - added calendar for 2022
:: Dec 2021
VBA sample question - ATO CorpTax - ATO company tax rates → User Defined function (UDF). Includes If and Select Case versions
:: Oct 2021
xlf :: VBA add a thermometer chart to the WS - a Quick Guide to the VBA needed to add a 'thermometer chart' to a WS. Technique: the ChartObjects.Add method
YMD WS date picker - Data Validation and Form Control versions of the Year-Month-Day worksheet selector interface - published version
:: Aug 2021
WS date - UNIX timestamp - added From Web URL string for query1.finance.yahoo.com/v7/finance/download
:: Jun 2021
Road map to the xlf-ymd-selector file - contains both Data Validation and Form Control versions of the Year-Month-Day worksheet selector interface WIP
:: Oct 2020
GetStrType function - evaluate the data type (limited) of element in a delimited string
:: Aug 2020
xlfGumbel function - VBA version of Gumbel distribution [Extreme Value]
Enterprise Risk Management - a list of ERM resources
Cells and ranges - added section 3.5
:: Jul 2020
Change function argument value - edit function arguments using VBA code
Programming VBA references - add a reference library programmatically
:: Jun 2020
Emulate DAYS360 function - custom WS function to emulate DAYS360 (US method - NASD)
VBA built-in constants - view ASCII values
WS controls and colors - quick guide to Shapes and ActiveX
Prevent data entry in WS TextBox - operation of the WS TextBox.Locked property
:: May 2020
Format the return value of a UDF - example :: FV of an annuity
Break out of a MsgBox Loop - Ctrl + Break, OSK, Cancel button
:: Apr 2020
Advanced filter to Filter function -video version
:: Feb 2020
Windows region settings - Windows 10
:: Jan 2020
Spreadsheets and risk - spreadsheet risk organizations, interest groups, and articles
Rules of logarithms - an introductory level guide - rule of logs - with examples for the HP 12C
:: Dec 2019
FSS Excel Windows or Mac - for 90045 participants - device discussion and recommendations
:: Oct 2019
WS ActiveX TabOrder code - achieve TabOrder using KeyDown event
:: Sep 2019
Normal curve tail area - combo area with step comparison
VBA's Select Case statement - correction to code 1
:: Jul 2019
Is year a leap year - VBA custom function
Normal distribution PDF :: VBA and GAS - code for Google GAS version of normal PDF with bonus VBA code
:: Jun 2019
Yahoo DST date adjustment - a correction to the daylight saving time error for ASX data sourced from Yahoo finance
Controlling code execution - examples of If...Then...Else, single-line, and multiline (block) versions
:: Mar 2019
Defined named with Excel for Mac - how to edit the address of a defined name in Excel for Mac
xldAddNameScope macro - VBA code using the Names.Add method - demonstrates Workbook scope and Worksheet scope
:: Feb 2019
WS add a battery chart - VBA code to construct chart - as a dashboard building block
:: Jan 2019
Black-Scholes Python UDF - an Excel function coded with Python and available via xlWings
WS add line element - VBA code AddConnector - as a dashboard building block
WS add circle element - VBA code AddShape - as a dashboard building block
:: Dec 2018
Add logo to banner - Shapes, AddPicture2, and ScaleWidth
Dollar to euro - decimal point 1.23 to decimal comma 1,23
WS date - ordinal indicator

Excel at Finance

xlf Excel at ... modeling, analysis and quantitative finance

Your browser does not support JavaScript!

Excel at Finance is a small collection of spreadsheet modelling, analysis, and quantitative finance items for Banking, Finance, and Business. We focus on Excel solutions and development.

Excel: useful keyboard shortcuts.

xlf Excel at ... quick links

xlf Excel at ... accordion content

xlf Sample content

Black-Scholes: function - for a Call or Put option on non dividend paying stock. Excel version and VBA code.

Box cancel button coding - for a Message Box (MsgBox), and an Input box (Application.Inputbox method).

Exponents: rules - a summary of the rules of exponents. Includes examples with HP 12C keystrokes.

How To - xlf Spreadsheet Modeling How To - video introduction.

Merton model of risky debt - equity value and the probability of default on debt payments. Debt valuation and implied credit spreads. Reconciles the equations of Saunders and Allan (2002) and Hull, Nelken and White (2004).

Monte Carlo: function - why does my Monte Carlo based option pricing function show two different values in the function arguments dialog box?

Option delta: function - function OptDelta returns the delta for European option on a non dividend paying stock. Includes overview, VBA code module plus graphics.

Percentage arguments: function - a UDF with percentage arguments called from Excel and VBA.

xlf References: Excel Finance, and VBA Finance

Albright S, and W Winston, (2019), Business Analytics: Data Analysis & Decision Making, 7th edition, Cengage Learning
Alexander M, and R Kusleika (2019), Excel 2019 Power Programming with VBA, Wiley
Alexander M, Kusleika R, and J Walkenbach (2019), Excel 2019 Bible, Wiley
Allman K, (2008), Reverse Engineering Deals on Wall Street with Microsoft Excel: A Step-by-Step Guide, Wiley Finance
Allman K, Laurito J, and M Loh, (2011), Financial Simulation Modeling in Excel: A Step-by-Step Guide, Wiley Finance
Baker K (2016), Optimization Modeling with Spreadsheets, Wiley
Benninga S, (2014), Financial Modeling, 4th ed., MIT Press
Benninga S, and T Mofkadi (2017), Principles of Finance with Excel, 3rd ed., Oxford Press
Benton C. J., (2017), Excel Pivot Tables & Introduction to Dashboards. The Step-by-Step Guide, Kindle Books
Charnes J, (2012), Financial Modeling with Oracle Crystal Ball and Excel, 2nd ed., Wiley Finance
Dalton S, (2007), Financial Applications Using Excel Add-In Development in C/C++, 2nd ed, Wiley Finance
Darbyshire P, and D Hampton, (2012), Hedge Fund Modelling and Analysis using Excel and VBA, Wiley Finance
Davison, (2014), Quantitative Finance: A Simulation-Based Introduction Using Excel, CRC Press
Dumrauf G, (2013), Bonds: A Step by Step Analysis with Excel, Amazon
Escobar M, (2018), Master Your Data With Excel and Power BI, 2nd edition, Holy Macro Books
Fairhurst D, (2012), Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, Wiley Finance
Fletcher S, and C Gardner (2009), Financial Modelling in Python, Wiley Finance
Gan G, (2017), An Introduction to Excel VBA Programming: With Applications in Finance and Insurance, 2nd ed., Taylor and Francis
Girvin, M (2013), Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic, Holy Macro! Books
Gottlieb I, (2013), Next Generation Excel: Modeling in Microsoft Excel for Analysts and MBAs, 2nd ed., Wiley Finance
Guerrero H, (2019), Excel Data Analysis: Modeling and Simulation, 2nd ed., Springer
Häcker J, and D Ernst (2017), Financial Modeling: An Introductory Guide to Excel and VBA Applications in Finance, Palgrave Macmillan
Heiberger R, and E Neuwirth, (2009), R Through Excel, Springer
Heydt M, (2014), Mastering pandas for Finance, Packt
Hilpisch Y, (2014), Python for Finance, Analyze Big Financial Data, O'Reilly
Holden C.W and S Venit (2014), Excel Modeling in Corporate Finance, Pearson
Jelen B, (2016), Excel 2016 in Depth, Que Publishing. Content Update Program
Jelen B, (2019), Microsoft Excel 2019 Inside Out, Pearson Education
Jelen B, and M Alexander (2019), Excel 2019 Pivot Table Data Crunching, Microsoft Publishing
Jelen B, and D K Dowell (2007), Excel for Auditors, Holy Macro! Books
Jelen B, and T Syrstad (2019), Excel 2019 VBA and Macros, Pearson Education
Johansson R, (2019), Numerical Python: Scientific Computing and Data Science Applications with Numpy, SciPy and Matplotlib, Apress
Kelliher C, (2022), Quantitative Finance with Python A Practical Guide to Investment Management, Trading, and Financial Engineering, Routledge
Korol J, (2016), Microsoft Excel 2016 Programming Pocket Primer, Mercury
Leong T-Y and M Cheong., (2014), Business Modelling with Spreadsheets: Problems, Principles and Practice, McGraw Hill
Mayes T.R., (2014), Financial Analysis with Microsoft Excel, Cengage Learning
McDonnell P, (2010), Optimal Portfolio Modeling: Models to Maximize Returns and Control Risk in Excel and R, Wiley Trading
McFedries P, (2019), Excel^® 2019 Formulas and Functions, Microsoft Press
Meissner G, (2013), Correlation Risk Modeling and Management: An Applied Guide including the Basel III Correlation Framework - with Interactive Models in Excel/VBA, Wiley Finance
Miller M, (2013), Mathematics & Statistics for Financial Risk Management, 2nd ed., Wiley Finance
Mireault P, (2017), Structured Spreadsheet Modelling and Implementation: A Methodology for Creating Effective Spreadsheets, 2nd ed., SSMI International
Mofkadi T and S Benninga, (2022), Financial Modeling Uses Excel and R, 5th ed., MIT Press
Oluwa S, (2019), Hands-On Financial Modeling with Microsoft Excel 2019: Build practical models for forecasting, valuation, trading, and growth analysis using Excel 2019 ,Pakt
Owen G, (2016), Using Microsoft Excel and Access 2016 for Accounting , Cengage Learning
Pignataro P, (2013), Financial Modeling & Valuation: A Practical Guide to Investment Banking and Private Equity, Wiley Finance
Proctor, S, (2008), Building Financial Models with Microsoft Excel: A Guide for Business Professionals, Wiley Finance
Ragsdale, C, (2016), Spreadsheet Modeling & Decision Analysis : A Practical Introduction to Business Analytics, Cengage
Raviv G, (2019), Collect, Transform and Combine Data using Power BI and Power Query in Excel, Pearson Education
Rees, M, (2018), Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA, Wiley
Roman, J, (2017), Analytical Finance: Volume I: The Mathematics of Equity Derivatives, Markets, Risk and Valuation, Springer
Romeo, G, (2020), Elements of Numerical Mathematical Economics with Excel: Static and Dynamic Optimization, Academic Press
Rouah F, and G Vainberg, (2007), Option Pricing Models & Volatility, Wiley Finance
Samonas M, (2015), Financial Forecasting, Analysis and Modelling: A Framework for Long-Term Forecasting, Wiley Finance
Sanderson S, and D Kun, (2024), Extending Excel with Python and R: Unlock the potential of analytics languages for advanced data manipulation and visualization, Pakt

Sawyer T, (2009)

Pro Excel Financial Modeling: Building Models for Technology Startups

Smith D, and P Winchie, (2011), Cash CDO Modelling in Excel: A Step by Step Approach, Wiley Finance
Syrstad, (2015), My Excel 2016, Que Publishing
Tung H, Lai D, and M Wong, (2011), Professional Financial Computing Using Excel & VBA, Wiley Finance
Van Der Post, H and A Schwartz, (2024), Excel For Finance 2024: A comprehensive guide to excel in quantitative finance (Functions, Pivots, Modelling, VBA & More), Reactive Publishingy
Walkenbach J, (2015), Microsoft Excel 2016 Bible: The Comprehensive Tutorial Resource, Wiley
Walkenbach J, (2013), Microsoft Excel 2013 Power programming with VBA, Wiley
Winston W, (2019), Microsoft Excel 2019 Data Analysis and Business Modeling, Microsoft Press
Yan Y, (2017), Python for Finance: Apply powerful finance models and quantitative analysis with Python, Packt

Maintained by: Dr Ian O'Connor, PhD. - located in Victoria, Australia.
Revised: Monday 28th of October 2024 - 03:31 PM, [Australian Eastern Time (AET)]
LastMod: [2024-10-28AEDT03:31:57+11:00]