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.
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
' 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
Views 150
Downloads 39
CodeID
DB ID