Close

January 25, 2013

Importing Edgar Database

We will be showing how to import database data from Edgar, with SQL code as well as SSIS Import Wizard configuration settings.

“Since 1934, the SEC has required disclosure in forms and documents. In 1984, EDGAR began collecting electronic documents to help investors get information”

First import the latest master.idx from http://www.sec.gov/edgar/indices/fullindex.htm and edit the file as shown below.

1-24-2013 5-27-40 PM 1-24-2013 5-28-05 PM

Create a database called Edgar

Create a table called master1 using T-SQL code below

Right click on Edgar database,click Tasks, then Import dat and use settings below.

 

USE [Edgar]
GO

/****** Object: Table [dbo].[master_02102013] Script Date: 2/10/2013 1:41:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[master_02102013](
[MasterID] [int] IDENTITY(1,1) NOT NULL,
[CIK] [varchar](50) NULL,
[Company Name] [varchar](200) NULL,
[Form Type] [varchar](50) NULL,
[Date Filed] [varchar](50) NULL,
[Filename] [varchar](50) NULL,
CONSTRAINT [PK_master_02102013] PRIMARY KEY CLUSTERED
(
[MasterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

1-24-2013 5-56-23 PM 1-24-2013 5-55-57 PM 1-24-2013 5-55-45 PM 1-24-2013 5-55-20 PM 1-24-2013 5-54-57 PM 1-24-2013 5-53-34 PM 1-24-2013 5-57-19 PM 1-24-2013 5-57-03 PM 1-24-2013 5-56-48 PM

Make sure Identity insert is checked off. Also make sure you select delete existing rows NOT append row data.

Example Search T-SQL Query after import is completed.

SELECT TOP 100000 [MasterID]
,[CIK]
,[Company Name]
,[Form Type]
,[Date Filed]
,[Filename]
FROM [Edgar].[dbo].[master]

Result

1-24-2013 9-07-07 PM

Coming soon – a script, to extract ftp filepaths from above results and possibly a script [in python] tp download from ftp.sec.gov all the files into appropriate columns in our database, which currently houses 53,398 rows of data.

————–

Scriot for downloading ONE file, from say data in first row of table created above. Save this as test.scr

anonymous
<your email address goes in here>
cd edgar/data/1000045/
ls *.txt
get 0001193125-13-046001.txt
quit

Script for a batchfile to execute above. Save as, for example: gosec.bat

cls
ftp -s:test.scr ftp.sec.gov