Central Gippsland: the home of excelatfinance.com [Photo: "smoke" January 2013]

XLF :: Contents


Google page search

Windows page search

With link descriptions visible, use Windows Ctrl + F to search. Report errors and omissions to ioconnor@excelatfinance.com.

Contents structure

Some items are listed more than once, and may be included as Excel/VBA and also Finance topics

    XLF Part I :: Spreadsheets
  1. Excel resources
    1. Excel - workbooks and worksheets
      1. Function keys - Excel function keys - F1, ... F12. Useful keys identified
      2. Dialog box launcher - a brief description of the Excel ribbon - dialog box launcher
      3. Name box - examples of common uses of the Excel Name Box
      4. Reference styles - Reference styles. A1 and R1C1. Switching styles. Relative, absolute, and mixed. Includes practical examples and worksheet files
      5. Cells: worksheet - active → current region - Worksheet cells - Active cell, Selection, and Current region.
        Includes: Single cell, multi-cell, identification, and navigation keys. Status bar - Ready mode, and Point mode
      6. Cell # errors - Excel errors with examples and xlsx file.
        Includes: #REF!, #NULL!, #DIV/0, #N/A!, #NAME?, #NUM!, and #VALUE!
    2. Excel - WB and WS styling
      1. Add blue banner - VBA code to add the xlf blue banner background to a worksheet
        Includes: Interior.Color, RGB, Selection.Resize
      2. Add logo to banner - VBA image from web link to the xlf blue banner background on a worksheet
        Includes: Shapes.AddPicture, msoFalse, msoTrue
    3. Excel - functions
      1. Logical functions Includes: WS IF, AND, OR XOR, TRUE, FALSE, and NOT
      2. Char function Includes: characters 1 to 32 (non printing), characters 33 to 127 (alpha numeric), and characters 128 to 255 (extended)
        Syntax: Worksheet: Indirect, Offset
      3. Match function Syntax: Match
      4. Offset function Syntax: Char
      5. Text function - this module demonstrates the Excel TEXT function with application to dates, percentages, currency formats, and numbers.
        Syntax: Worksheet: Char, Date, Dollar, Text, Today, Year
      6. Standard normal probability tables
      7. Excel functions with array arguments - a list of WS functions with explicit array arguments.
        Syntax: Worksheet (CSE): MDETERM, MINVERSE, MMULT, and TRANSPOSE

  2. Data, Dates and Dollars
    1. Data - manipulation
      1. Stock price volume data - web download - example - how to download price data from Yahoo finance. Step by step procedure, with save to a comma separated value (CSV) file
      2. Data validation w/ multiple lists - module that demonstrates the use of data validation with multiple level Source lists. Related material: the xlf Stock Analyser project
      3. Out of data alert - create an Out of Data alert using conditional formatting. Related material: the xlf Stock Analyser project
      4. Interpolation - linear NA → Formula - this module demonstrates linear interpolation. Identify each NA error block, then replace each NA with R1C1 formula
        Includes: TDCs % and &, IsNA, IsNumeric, Offset, WorksheetFunction, Set, Watches Window, Application.CutCopyMode, and PasteSpecial
      5. Interpolation - last known value (LKV) - this module describes the last know value (LKV) interpolation method with application to situations where price-volume data points are missing from the data set
      6. M data sheet dynamic range [EandA] - this document is intended for use by participants in the 90045 session series. It is of limited use if read out of context. Thew exercise includes the missing data interpolation worksheet (named M Data dev in this version) and development of a text based dynamic range name
        Includes: Excel functions AND, IF, INDIRECT, ISBLANK, NOT, and INDIRECT(INDIRECT...) when used in a Data Validation environment
      7. Vector to array - VBA sub procedure to link an array to a row vector, using cell formulae in the target.
        Includes: For...Next loop. CurrentRegion, FormulaR1C1, Cells property.
      8. Arrays and vectors - worksheet arrays and vectors. The array as a data structure, and mathematical element wise array operations
        Includes: Scalar, column vector, row vector, and multidimensional arrays. Curly braces, and element wise operations with examples (the Carrot Washer)
    2. Data - analysis
      1. Data analysis - subtotals - subtotals, manual, single, multi and nested. Subtotal format, and copy visible cells. Subtotal function - syntax, and as a dynamic link to labels
        Includes: Average, Count, Counta, Max, Min, Product, StDev. StDevP, Sum, Var, VarP
      2. Data descriptive statistics, and covariance - stock price log returns, variance and standard deviation.
        Includes: Statistical functions, ATP descriptive statistics, and covariance. COVARIANCE.P, COVARIANCE.S. Full covariance matrix with dynamic reference COVARIANCE.S and INDIRECT
      3. Count decimal places in a text string - this module describes the last know value (LKV) interpolation method with application to situations where price-volume data points are missing from the data set
        Syntax: VBA - InStr, Len, Replace, and Rtrim
      4. Count cells with error values Use IS functions (ISERROR, ISERR, ISNA), and COUNTIF to count error cells in an Excel worksheet.
        Includes: COUNTIF, IF, ISERROR, ISERR, ISNA, SUMPRODUCT and double negation
      5. Excel 2010 Power Query → Excel 2016 Get and Transform - an example of Data tab Get and Transform (data) items on the ribbon, plus examples of all Excel 2010 and Excel 2016 ribbon tabs!
    3. Dates
      1. Date comparisons - different spreadsheet applications - a comparison of dates and serial numbers across three spreadsheet applications - MS Excel, LibreOffice Calc, and Google Sheets. Includes the 1900 leap year error, the spreadsheet epoch, and negative serial dates
      2. A list of ASX non trading days - a list of non trading days calendar dates for the Australian Stock Exchange (ASX) - stored in an Excel dynamic range - OFFSET and COUNT functions. Custom date format
        Syntax: worksheet - OFFSET, and COUNT
      3. Advanced filter - weekdays to trading days - advanced filter - Excel based - filter weekday list to hide public holidays.
        Includes: COUNTIF function, series, dates, custom function, advanced filter, criteria range, business days, weekdays, criteria range formula
        Syntax: worksheet - COUNTIF
      4. Business (trading) day, next or previous - demonstrates how to determine if a date is a business day, and if not return the date of the next or previous business day. Uses the Excel WORKDAY function
      5. The DateV dynamic range - part of the stock analyser project - shows how to construct a dynamic range date vector. This is a core component in the moving data windows that drives the descriptive statistics, and dashboard charts in the xlf Analyser Project
        Syntax: worksheet - EDATE, FIND, LEFT, SEARCH, and WORKDAY
      6. VBA time concepts - includes examples of TimeSerial, TimeValue, and Time functions, plus the VBA Date data type
        Syntax: VBA - TimeSerial, CDbl, TimeValue, and Time
      7. Is date within range of dates - FY_17 - a VBA function that checks if a date is within a range of dates. Returns Boolean TRUE or FALSE.
        Includes: If...Then construct with two logical tests. AND operator. VBA DateValue function
      8. Trading date function :: ASXTD2016(Dte) [EandA] - an Exercise with Answer - write a Private Function procedure that determines if a particular date is a trading day for the Australian Stock Exchange …
        Includes: Dates in Array type String, Dates in Array type Integer, Dates in Array type Date. CVErr, DateSerial, DateValue, IsError, Split, Time, LBound, UBound, vbSaturday, vbSunday, Weekday
    4. Dollars
      1. Rate of return - discrete returns, net returns, gross returns, log returns, cumulative returns, with sample worksheet - download from Excel web app
      2. Price to return [EandA] - an Exercise with Answer - 1. Write a private function named P2R with VBA caller, and 2. Write a UDF function named Price2Return with Excel (WS) caller. Components: Option Base 1 declaration, ascending and descending order of the price vector or array, and aspects of the array dimension
      3. Interest - simple and compound - a summary of the simple and compound interest, incorporating algebraic formulae, examples, and Excel equations and functions where available.
        Syntax: WS Functions: FV, PV, and ACCRINT
      4. Interest rate risk - gap analysis - Rate sensitive assets (RSA) and rate sensitive liabilities (RSL). Interest rate risk (IRR) measurement and reporting. Gap analysis with Excel based spreadsheet, bucket end points, mid points, quasi delta NII, and actual annual delta NII.
      5. Rules of exponents - rules of exponents material used in the xlf presentation series. This page includes numerical examples, and keystrokes for the Hewlett Packard 12C financial calculator.

  3. Charts and the User Interface
    1. Price Volume combo charts
      1. Price volume chart [1] - Excel 2013 - create a price volume (combo) chart from a stock data time series.
        Includes: Chart features - the date axis includes non trading days; a price series Currency format and volume series custom Million format are applied
      2. Price volume chart [2] - Excel 2013 - create a price volume (combo) chart from a stock data time series.
        Includes: Chart features - the date axis excludes non-trading days; a price series Currency format and volume series custom Million format are applied
      3. Price volume chart [3] - Excel 2010 - Create a price volume chart from a stock data time series.
        Includes: Chart features - the date axis excludes non-trading days; a price series Currency format and volume series custom Million format are applied
    2. X-Y (Scatter) charts
      1. X-Y scatter plot with correlated random numbers - Random number generator - using volatile functions - with number of observations n switch. Dynamic range as a statistical data reference. Dynamic range as a chart series. Creating an X Y scatter chart
    3. Other charts
      1. Excel 2013 Power Map ribbon group - an example of Power Map with 3D maps with data from the 100 data records set
      2. Excel 2013 Power View - an example of Power View and Power BI desktop with data from the 100 data records set
    4. User interface examples
      1. On sheet elements - shapes - add a Rectangle and other elements to the drawing layer, apply shape format, and text format. Include a WS hyperlink, Protect Sheet, and apply a password
        Includes: Drawing tools, Shapes, Fill and Outline. TextBox elements - Graphic Tools, Signs and Symbols, Rotate and Hyperlink
      2. On sheet controls - dynamic chart interface [EandA] - an Exercise with Answer - develop an on sheet interface - three chart types, chart location, and dynamic chart series vectors
        Includes: Form controls - TextBox, and Group Box, ActiveX controls - CheckBox, OptionButton, and Image. Chart Callers for Form and ActiveX.
        Syntax: VBA - procedures :: cmdCharter_Initialize, HideCaller, UnHideCharter, chart dates and return vector, and chart updater. ChartInterfaceReset
      3. On sheet controls - default values and passing arguments - develop a box sequence interface using form controls.
        Includes: Form controls - TextBox, Button and Check Box. Pass the Check Box value to the procedure ByVal as Boolean. Code a quasi click event for the BoxSequence macro
        Syntax: VBA - procedures :: Sub BSCallOptBoxes(ByVal Switch As Boolean), and Private Sub BoxSequence_Click()

    XLF Part II :: Spreadsheet automation [TOP ] [Toggle]
  4. VBA concepts
    1. Visual Basic Editor (VBE)
      1. VBE interface Elements of the VBA editor - interface
        Includes: Menu bar, Tool bars, and Windows - project explorer, properties window, code window, immediate window, locals window, and watch window
      2. Code window elements Code writing, blinking insertion point (BIP), and margin indicator bar
        Includes: Key word and Comment text, the Blinking Insertion Point, and Margin indicator bar: Call return, Execution point, and Break point
    2. Variables
      1. Variables 1
      2. Numbers - integer to floating Formulas and VBA code to return minimum and maximum values for Integer, Long, LongLong, Single, and Double. All formulas are binary (base 2)
      3. Variables and constants - scope
      4. Name 255 character limit
      5. Line continuation and its limit VBA's line-continuation (represented by the <space - underscore - enter> sequence) and the 24 limit. Also includes an example of the Excel 64 nested functions limit
        Includes: Concatenation & and +, Application.Rpt, and FormulaR1C1,
    3. Procedures
      1. Function procedures - user defined an introductory guide to Excel UDFs. 1. No arguments, 2. a fixed number of arguments, 3. fixed arguments with multi cell array, 4. optional arguments, and 5. an indeterminate number of arguments
        Includes: Volatile, FunctionWizard, FormulaArray, IsMissing
      2. Sub procedures - syntax an introductory guide to the sub procedure syntax, and writing and executing sub procedures.
        Includes: Syntax, Public and Private declaration, Static declaration, Passing arguments, ByRef and ByVal, and Exit Sub
        Syntax: Sub, End Sub, Exit Sub, Empty, IsEmpty, TypeName, VarType
      3. Calling private procedures - use the Application.Run method to call private procedures in other modules. Demonstrates private macros and functions.
        Includes: Private, Application.Run (arguments by position and by name)

    4. Controlling code execution
      1. If...Then...Else statement with application to a tax payable case example, a review of the Excel IF function, with line-break formula (Alt + A), and relative name addressing
      2. Select Case statement with application to a tax payable case example (introduced in the If...Then...Else statement module)
      3. For...Next statement application of the VBA For...Next statement to a discount factor example
    5. Arrays
      1. VBA arrays VBA arrays - static, dynamic, and aspects of dimension. Includes Dim, Redim, Preserve, UBound, and LBound
        Syntax: VBA LBOUND, UBOUND
      2. Array dimensions - number VBA function GetArrDim - get array dimension. Returns an integer value of the number of dimensions in a VBA array
        Syntax: VBA - Err.Number, IsNumeric, On Error, and UBOUND
      3. Array dimensions - limit
        Syntax: VBA - Debug.Print, Err.Number, LBOUND, On Error, vbNewLine, and UBOUND
      4. Array and subarrays [QandA] An exercise with 3-D arrays, extracting dimensions, and an application to income tax. Tax table to WS array. Tax table to VBA array.
        Includes: UBound, Select...Case, If..Then...ElseIf, Vlookup, Month, Year, MacroOptions
      5. Array power 2 [EandA] - an Exercise with Answer - 1 Write a user defined function (UDF) named ArrPwr2 that takes one argument, a range object, and returns the square (^ 2) of the argument as an array. 2 Write a test procedure, named TestArrPwr2 to pass the Sheet1!B3:C5 array to the ArrPwr2 UDF, and assign the return value to a variable named Ans of type Variant
        Includes: Function with Range argument, double For...Next loop, Process Header row and Data row(s), Display InputBox method (various), and Write output to WS

  5. Application, Workbooks, Worksheets, and Range
    1. Worksheets
      1. Does worksheet name exist from the Q and A series. Shows how to determine if a worksheet name exists in a workbook
        Includes: Empty, Goto, OnError, Sheets(Index)
    2. Range
      1. Range object (1) - part 1 - an overview of the Application to Range object hierarchy. Range object examples include single cell, and multi cell;
        Includes:Loop through range elements with the .Item property. Assign values to an array of type Variant, and read with the IsEmpty function
        Syntax: .Cells, .Item, and Offset
      2. Range object (2) - part 2 - range object examples include single cell, and multi cell; writing the range to a VBA array - For...Next loop with dimension counter, For...Next, and For Each...Next loops. Write the return value arrays to the Immediate Window. Pass arguments to the Write sub procedure. Bonus code - how to clear the Immediate Window with SendKeys.
        Includes: Cells, Column, Count, Left, LBound, Offset, Range, ReDim, Resize, SendKeys, UBound, vbNewLine, WorksheetFunction
      3. Copy paste - example this module reviews Worksheet copy and paste, then illustrates VBA code for Copy method and PasteSpecial method, Copy with Destination, and Value assignment
        Includes: ClearContents, Copy(Destination), Offset, Paste, PasteSpecial(Paste, Operation, Resize, SkipBlanks, Transpose), Range(n).Range("A1"), Set, ScreenUpdating
        Syntax: PasteSpecial method, and Copy(Destination) method
      4. Excel range - address of first and last cell this module use of selected functions from Excel's lookup and reference function category, and CELL function from the information category
        Includes: examples that return the address of the first cell, last cell, and address of range. Value of first cell and last cell.

    XLF Part III :: Spreadsheet forms and controls [TOP ] [Toggle]
  6. Communicating with the User
    1. UserForm - Concepts and Applications
      1. Initialize event - example this module contains VBA code to initialize UserForm objects. RefEdit, and the TextBox object with DropButtonStyle
        Includes: DropButtonStyle, fmDropButtonStyleReduce, fmShowDropButtonWhenAlway, ShowDropButtonWhen
      2. ActiveX controls - toggle button VBA Macros - Full Screen, and Clear Screen.
        Includes: Control properties: Caption, Enabled. Application properties: Caption, DisplayFormulaBar, DisplayStatusBar, ExecuteExcel4Macro. ActiveWindow properties: Caption, DisplayGridlines, DisplayHeadings, DisplayHorizontalScrollBar, DisplayVerticalScrollBar, and DisplayWorkbookTabs
      3. NPV engine this module develops code for the NetPV engine, with VBA NPV, WS NPV and NPV from first principles.
        Includes: Option Private Module, # and % TDC's, assignment of range values to VBA arrays, and passing arguments to sub routines
      4. UserForm alternatives (1) - ListBox and ComboBox - a module that demonstrates the use of UserForm alternatives - Data Validation from List, on sheet ListBoxes and ComboBoxes - no VBA
    2. Boxes
      1. Box cancel button coding Box Cancel button Coding: for a Message Box (MsgBox), and an Input box (Application.Inputbox method)
        Includes: MsgBox, Application.InputBox, and Evaluate
      2. MsgBox icons A quick guide to VBA MsgBox() function icons and a comparison to Data Validation Alert icons
        Includes: , VbMsgBoxStyle enumerations, MsgBox enumerated constants, Data Validation Error Alert Style icons, XlDVAlertStyle enumerations. VBA code for MsgBox function, and Validation Error Alert
      3. InputBox default - type 8 Describes the InputBox method Default value - as a Range Name, property and string, and - as a Range Address, address and name
        Includes: VBA Quick Info, Name Manager, WB scope Constant, InputBox method Default parameter, Locals Window,
        Syntax: Name.Name, Range.Address, and Range.Name properties
      4. Built in dialog boxes Built in dialog boxes. Examples - Print, Save As, and Workspace. )
        Includes: Show method, Dialogs property, and XlBuiltInDialog collection
    3. Interface - GUI, Ribbon, and Selectors
      1. Ribbon XML - an introduction - add a new group to an existing Excel ribbon tab, write the XML code, and VBA Callbacks
        Includes: Ribbon customization with XML :: tab, group, button, insertaftermso, supertip, imagemso, onaction. Demonstrates development of the customUI file

  7. Excel and Windows environment
    1. Excel
      1. What version of Excel am I using? - use the Excel File tab to discover the version of Excel you are using
      2. Get Excel version macro - GetXLVer - get Excel version VBA macro. Returns a message box displaying the Excel version details.
        Syntax: Application BUILD, VERSION, OPERATINGSYSTEM properties, and ENVIRON function

      3. GetFT formula - a custom formula that returns the formula of the given reference as text. Incorporates a dynamic relative address of the reference cell.
        Syntax: WS functions: ADDRESS, FORMULATEXT, and ISFORMULA

      4. GetCF function - a custom function. Returns the formula of the given reference as text
        Includes: VBA Address, Formula, IsMissing, ReferenceStyle, Text, Value. On Error Goto
        Syntax: WS functions: ADDRESS, FORMULATEXT, and ISFORMULA

      5. Excel functions keys - function keys F1 to F12 with description.
      6. Set Excel window dimension to 720p - demonstrates an easy way of setting the Excel window to 720P resolution for uploading screen capture to sites such as YouTube
        Includes: Application, WindowState, Top, Left, Height and Width properties. Pixels to Points conversion
        Syntax: Application Height and Width properties
    2. VBA
      1. Opening macro enabled workbooks - follow these steps if a Security Warning appears when you open the session workbook. Security alert, enable content
      2. Require user to enable macros - present a Warning Message screen requiring the user to enable macros to continue using the workbook
      3. VBA versions - VBA versions - Excel 2003 to 2013. Applies the conditional #If...Then...#Else directive to a description of the GetCF function
        Includes: #If...Then...#Else compiler directive
    3. Windows
      1. Windows voice - text-to-speech is available in Excel with the VBA Speech.Speak method. This module shows how to change the voice in the Windows Time and Language settings
        Includes: Application.Speech.Speak method. Passing arguments to a sub procedure
      2. Windows region settings - demonstrates changing the Windows > Region Settings environment. The impact on Excel's currency, time, and date is illustrated. The rule of 2029 is discussed

    XLF Part IV :: Applications in finance [TOP ] [Toggle]
  8. Options and derivatives
    1. Option pricing - Black Scholes model
      1. Black Scholes Excel and VBA - European option on a non dividend paying stock - using Excel cell formulas (d1, d2, Nd1, Nd2, Call and Put), VBA UDF function code and testing procedure
        Includes: WS Norm.S.Dist, VBA Format, Norm_S_Dist, TypeName, and optional arguments
      2. Black Scholes on the HP 10bII+ - European option on a non dividend paying stock - using Hewlett Packard calculator - 5 steps (d1, d2, Nd1, Nd2, Call and Put)
        Includes: HP 10bII+ keystrokes
      3. Black Scholes in Google Sheets and GAS - the BS model written in JavaScript. JavaScript functions for cumulative normal distribution: xlfBSCall, xlf NormSDist, and xlfBSPut. Application of the Abramowitz (1972) equations 26.2.1 for the N(x) approximation
        Includes: JavaScript Math.exp, Math.log, Math.pow, Math.sqrt. Approximation to the cumulative normal distribution function N(x) from Abramowitz (1972) equations 26.2.1 p931, 26.2.17 p932; and Bellalah (2008, Appendix 6, p126), constants a = 0.2316419; a1 = 0.31938153; a2 = -0.356563782; a3 = 1.781477937; a4 = -1.821255978; a5 = 1.330274429;
      4. Black Scholes - implied volatility and Goal Seek - from the Q and A series. Shows how to estimate implied volatility across multiple options using Goal Seek without selecting individual instruments
        Includes: Range.GoalSeek method with syntax. Array range objects, arrays of numbers, and looping through array elements
    2. Simulation
      1. Triangle numbers [EandA] - an Exercise with Answer - return the triangle sum of the arithmetic sequence 1 + 2 + 3 + ... + (n -1) + n
        Includes: x-y charts of the triangle, with VBA code to create the triangle series points. Finance aspects - the number of elements in the portfolio variance matrix