SEDOL Validation (VBA Function)

ValidSEDOL function

This article describes the formula syntax and usage of the ValidSEDOL user defined function in Microsoft Excel.

NOTE: This function requires that the VBA code be implemented in the target workbook, or be contained in an addin. If contained in an addin, all parties to whom the code is distributed must also have the addin installed.

Description

Returns a boolean result (True or False), indicating whether or not a given String passes the Stock Exchange Daily Official List (SEDOL) code pattern and Luhn algorithm. You can use this function either as a worksheet function (formula) and/or call it from VBA.

Syntax

ValidSEDOL(SEDOL,[New])

The ValidSEDOL function has the following arguments:

  • strSEDOL: Required. The SEDOL code that you want to validate. This argument can be passed as a String Constant, or reference a cell range containing the SEDOL.
  • blnNew: Optional. True if you want to validate the code against the convention established post 26 January 2004, otherwise False (default). SEDOLs issued after 26 January 2004 are prefixed with an alpha character. Omit, or choose False if you are not sure of the issuing date.

Remarks

  • Returns True if strSEDOL passes the validation check, otherwise returns False.
  • If called as a worksheet function, returns #VALUE! if strSEDOL is an error value (e.g. #N/A, #REF!, #VALUE! etc.)
  • If called as a VBA function, throws a Runtime Error ’13’: Type Mismatch if strSEDOL is an error value.

Example

Use in formula

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

SEDOLFormulaResultRemark
3208986=ValidSEDOL(A2, TRUE)FALSEFALSE because this SEDOL was issued prior to January 26, 2004. The 2nd function argument is TRUE, which means that it must match the new SEDOL convention to yield TRUE.
B09LQS3=ValidSEDOL(A3, FALSE)TRUEThis is a valid SEDOL by both old and new convention.

Use in VBA

Basic Case – validate a single SEDOL

Copy this code to a standard module in your workbook and click ‘Run’ (F5).

Practical Case – Validate a table of SEDOLs

Input a series of SEDOL codes into Sheet1 range A1:A10. Copy this code to a standard module in your workbook and click ‘Run’ (f5). The result will be a message box for each SEDOL that is NOT valid.

Installation

References

Comments are closed