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
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, Apress
- 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 ]