EdgarCIK

Search for company long name in Edgar and returns its CIK
Using SEC.gov site https://www.sec.gov/cgi-bin/cik_lookup
Covers option of having no company found as well as option if having more than 1 company listed.
Also covers options that Edgar listed in their search page with having or not having "The", "Co", "co." , "ltd", ... etc.

CodeFunctionName

What is this?

Public

Tested

Original Work

Function EdgarCIK(CompanyLongName)
    ' Search for company long name in Edgar SEC.gov https://www.sec.gov/cgi-bin/cik_lookup and return CIK
    '                https://www.sec.gov/edgar/search
    ' CIK is the ID that Edgar uses to identify every company
    '
    '    Needs NavigatePost(), VBInstr(), CutString()
    '
    Rett = ""
    CoFound = 0
    If CompanyLongName = "" Then GoTo ByeBye

    CoLong2 = Replace(CompanyLongName, "&", "%26")

    PostURL = "https://www.sec.gov/cgi-bin/cik_lookup"
    Page1 = NavigatePost(CStr(PostURL), "company=" & CStr(CoLong2))
    DoEvents
    ' We are either at page of company
    ' Or, we landed on a search page

    ' CompanyLongName is the name in our DB
    ' If CoLong not found, search again using abbreviated names as below
    '    Remove all commas
    '    Replace & wiht HTML & = &
    '    company > co
    '    corporation > corp
    '    incorporation > inc
    '    limited > ltd
    '    international > int
    '    the > remove the at begining of name
    '    Ignore dot at end of name

    Search4 = CompanyLongName
    GoSub Search4CIK
    If CoLongFound1 = 0 Then ' If result returns more than 1 company
        CoLong2 = Replace(CompanyLongName, "&", "&")
        Search4 = CoLong2
        GoSub Search4CIK
    End If

    If CoLongFound1 = 0 Then ' If result returns more than 1 company
        CoLong2 = CompanyLongName
        CoLong2 = Replace(CoLong2, ",", "")
        CoLong2 = Replace(CoLong2, "COMPANY", "CO", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "CORPORATION", "CORP", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, ", INCORPORATED", " INC", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "INCORPORATED", "INC", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "INDUSTRIES", "IND", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "INTERNATIONAL", "INT", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "LIMITED", "LTD", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "&", "&")
        If Left(CoLong2, 4) = "THE " Then CoLong2 = Mid(CoLong2, 5)
        If Right(CoLong2, 1) = "." Then CoLong2 = Left(CoLong2, Len(CoLong2) - 1)
        Search4 = CoLong2
        GoSub Search4CIK
    End If

    If CoLongFound1 > 0 Then
        CoFound = CoLongFound1
        CoPage = Page1
    Else
        CoLong2 = CompanyLongName
        CoLong2 = Replace(CoLong2, ",", "")
        CoLong2 = Replace(CoLong2, "COMPANY", "CO", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "CORPORATION", "CORP", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, ", INCORPORATED", " INC", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "INCORPORATED", "INC", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "INDUSTRIES", "IND", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "INTERNATIONAL", "INT", , , vbTextCompare)
        CoLong2 = Replace(CoLong2, "LIMITED", "LTD", , , vbTextCompare)
        If Left(CoLong2, 4) = "THE " Then CoLong2 = Mid(CoLong2, 5)
        If Right(CoLong2, 1) = "." Then CoLong2 = Left(CoLong2, Len(CoLong2) - 1)

        CoLong3 = Replace(CoLong2, "&", "%26")
        CoLong3 = Replace(CoLong3, " ", "%20")

        Page2 = NavigatePost(CStr(PostURL), "company=" & CStr(CoLong3))
        DoEvents
        Page1 = Page2
        CoLong2 = Replace(CoLong2, "&", "&")
        Search4 = CoLong2
        GoSub Search4CIK
        CoLongFound2 = VBInstr(" " & UCase(Search4) & "", Page1)
        If VBInstr(" <strong >0 </strong >", Page1) > 0 Then
            Rett = CoLong2 & " not found in Edgar"
        ElseIf CoLongFound1 > 0 Then
            CoFound = CoLongFound1
            CoPage = Page1
        ElseIf CoLongFound2 > 0 Then
            CoFound = CoLongFound2
            CoPage = Page1
        Else
            Rett = "Not found in Edgar"
        End If
    End If

    If CoFound > 0 Then
        Page3 = Mid(CoPage, CoFound - 50, 50)
        Rett = CutString(Page3, """ >", " </a")
    End If

    GoTo ByeBye

Search4CIK:
    CoLongFound1 = VBInstr(" " & Search4 & " ", Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & vbCrLf, Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & vbCr, Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & vbLf, Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & ". ", Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & "." & vbCrLf, Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & "." & vbCr, Page1)
    If CoLongFound1 = 0 Then CoLongFound1 = VBInstr(" " & Search4 & "." & vbLf, Page1)
    Return

ByeBye:
    EdgarCIK = Rett
End Function

CompanyLongName