Computing the ISIN checksum in Excel

Julian D. A. Wiseman

Contents: Publication history; Introduction; The Code

Publication history: only here. Usual disclaimer and copyright terms apply.

Introduction

To facilitate settlement and hence trading of securities, each security has a unique identifying code. There are several types of identifying code, the one that is used internationally being the ISIN, or International Securities Identifying Number. Every security in almost every country has an ISIN (possibly excepting Colombia, whose government bonds—uniquely—appear to lack an ISIN). The structure of an ISIN is defined in ISO 6166, and the algorithm is described by Wikipedia. Defined below is an Excel VBA function LastDigitISIN(ElevenChars As String) As String that returns the last digit of an ISIN given the first eleven.

UK securities also have a SEDOL number, also defined in Wikipedia. The function LastDigitSEDOL(SixChars As String) As String returns the last digit given the first ≤6 characters. If the input is shorter than six characters it is left-padded with zeroes.

ISINs of UK securities are typically made by prefixing the SEDOL with “GB00” (and perhaps with “IE00” for Irish securities) and suffixing with the ISIN checksum. ISINfromSEDOL6(CountryCode As String, SixChars As String) As String computes the ISIN given the first six characters of the SEDOL.

The code was written for Microsoft Excel (though may well work in other environments). It is © Julian D. A. Wiseman 2006 and 2007, and may be distributed under the terms of the GNU General Public License.

The Code

Option Explicit

' Given the first eleven characters of an ISIN, this calculates the twelfth character, the checksum.
' © Julian D. A. Wiseman 2006 & 2007. Believed correct. If it doesn’t always work then tough—it is free.
' Latest version available via http://www.jdawiseman.com/papers/trivia/isin.html

Public Function LastDigitISIN(ElevenChars As String) As String
	Dim i As Integer, CheckSumDigits As String, TotalScore As Integer, Char As String
	If Len(ElevenChars) <> 11 Then
		LastDigitISIN = "LastDigitISIN: Error — length of """ & ElevenChars & """ not 11 characters."
		Exit Function
	End If  ' Len(ElevenChars) <> 11
	CheckSumDigits = ""
	For i = 1 To 11
		Char = UCase(Mid(ElevenChars, i, 1))
		If Char >= "0" And Char <= "9" Then
			CheckSumDigits = CheckSumDigits & Char
		ElseIf Char >= "A" And Char <= "Z" Then
			CheckSumDigits = CheckSumDigits & (10 + Asc(Char) - Asc("A"))
		Else
			LastDigitISIN = "LastDigitISIN: Error - character " & i & " of """ & ElevenChars & """ neither 0 to 9 nor A to Z."
			Exit Function
		End If
	Next i
	TotalScore = 0
	For i = 1 To Len(CheckSumDigits)
		If (i + Len(CheckSumDigits)) Mod 2 Then
			TotalScore = TotalScore + Val(Mid(CheckSumDigits, i, 1))
		Else
			TotalScore = TotalScore + Choose(1 + Val(Mid(CheckSumDigits, i, 1)), 0, 2, 4, 6, 8, 1, 3, 5, 7, 9)
		End If  ' 0 = (i + Len(CheckSumDigits)) Mod 2
	Next i
	LastDigitISIN = Format((130 - TotalScore) Mod 10, "0")
End Function  ' LastDigitISIN(ElevenChars As String) As String


Public Function LastDigitSEDOL(SixChars As String) As String  ' SixChars front padded with zeroes
	Dim i As Integer, Char As String, Multiplier As Integer, TotalScore As Integer
	If Len(SixChars) > 6 Then
		LastDigitSEDOL = "LastDigitSEDOL: Error - length of """ & SixChars & """ exceeds 6 characters."
		Exit Function
	End If  ' Len(SixChars) > 6
	For i = 1 To Len(SixChars)
		Multiplier = Choose(i + 6 - Len(SixChars), 1, 3, 1, 7, 3, 9)
		Char = UCase(Mid(SixChars, i, 1))
		If Char >= "0" And Char <= "9" Then
			TotalScore = TotalScore + Char * Multiplier
		ElseIf Char >= "A" And Char <= "Z" Then
			TotalScore = TotalScore + (10 + Asc(Char) - Asc("A")) * Multiplier
		Else
			LastDigitSEDOL = "LastDigitSEDOL: Error — character " & i & " of """ & SixChars & """ neither 0 to 9 nor A to Z."
			Exit Function
		End If
	Next i
	LastDigitSEDOL = (870 - TotalScore) Mod 10
End Function  ' LastDigitSEDOL(SixChars As String) As String


Public Function ISINfromSEDOL6(CountryCode As String, SixChars As String) As String
	ISINfromSEDOL6 = CountryCode & "00" _
		& Application.WorksheetFunction.Rept("0", 6 - Len(SixChars)) & SixChars & LastDigitSEDOL(SixChars)
	ISINfromSEDOL6 = ISINfromSEDOL6 & LastDigitISIN(ISINfromSEDOL6)
End Function  ' ISINfromSEDOL6(CountryCode3166 As String, SixChars As String) As String

Julian D. A. Wiseman