Contents
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.
| SEDOL | Formula | Result | Remark |
|---|---|---|---|
| 3208986 | =ValidSEDOL(A2, TRUE) | FALSE | FALSE 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) | TRUE | This 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).
|
1 2 3 4 5 6 7 8 |
Public Sub Demo1() Dim blnIsValid As Boolean Const strSEDOL As String = "B126KH9" blnIsValid = ValidSEDOL(strSEDOL) Call MsgBox(Prompt:=WorksheetFunction.Trim$(strSEDOL & " is " & IIf(blnIsValid, "", "not") & " a valid SEDOL!")) End Sub |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Public Sub Demo2() Dim rngSEDOLs As Excel.Range Dim rngCell As Excel.Range Set rngSEDOLs = Sheet1.Range("A1:A10") For Each rngCell In rngSEDOLs If Not ValidSEDOL(rngCell.Text) Then Call MsgBox(rngCell.Text & " is not a valid SEDOL!") End If Next rngCell End Sub |
Installation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
Option Explicit Option Private Module Public g_objREGEX As Object Public Function CharToDigit(ByVal strSecurity As String) As Variant Dim objMatch As Object Dim objMatches As Object Dim strStagingString As String If g_objREGEX Is Nothing Then Set g_objREGEX = CreateObject("vbscript.regexp") End If With g_objREGEX .Global = True .IgnoreCase = False .Pattern = "([A-Z])" End With strStagingString = Left$(StrConv(strSecurity, vbUnicode), Len(strSecurity) * 2 - 1) Set objMatches = g_objREGEX.Execute(strStagingString) For Each objMatch In objMatches strStagingString = Replace$(strStagingString, objMatch.Value, Asc(objMatch.Value) - 55) Next objMatch CharToDigit = Split(strStagingString, Chr$(0)) End Function Public Function SEDOLPattern(ByVal strSEDOL As String) As Boolean If g_objREGEX Is Nothing Then Set g_objREGEX = CreateObject("vbscript.regexp") End If With g_objREGEX .IgnoreCase = False .Pattern = "^[B-DF-HJ-NP-TV-Z0-9]{6}[0-9]{1}$" End With SEDOLPattern = g_objREGEX.Test(strSEDOL) End Function |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Public Function ValidSEDOL(ByVal strSEDOL As String, Optional ByVal blnNew As Boolean = False) As Boolean Dim lngCheckNum As Long Dim varNums As Variant Dim lngResult As Long strSEDOL = UCase$(Format$(strSEDOL, "0000000")) If Not SEDOLPattern(strSEDOL) Then Exit Function If blnNew Then If IsNumeric(Left(strSEDOL, 1)) Then Exit Function lngCheckNum = CLng(Right$(strSEDOL, 1)) varNums = Join$(CharToDigit(Left$(strSEDOL, 6)), ",") lngResult = (10 - (Evaluate("sumproduct({" & varNums & "},{1,3,1,7,3,9})") Mod 10)) Mod 10 ValidSEDOL = CBool(lngCheckNum = lngResult) End Function |