Create an XML Information Table for Form 13F Using Excel

See all How Do I pages

   

Jump to sections:

• Step 1—prepare the information table in Excel
• Step 2—save the schema files
• Step 3—enable the Developer tab in Excel
• Step 4—map the Excel file
• Step 5—export the information table to an XML file
• Step 6—upload and validate the XML file in the electronic form 13F

Follow the steps below to prepare a Form 13F Information Table in Excel (2010 - 2016) for electronic submission to EDGAR.

Step 1—prepare the information table in Excel

The information table should have exactly 13 columns with corresponding column headers as depicted below.

Data input errors will cause subsequent schema validation errors when the table is exported to XML. To avoid data input errors, please ensure the following:

• No blank rows above or below the table.
• Header information must be on one row (for example, Name of Issuer, Title of Class, Investment Discretion, etc.).
• CUSIP number must be nine (9) characters.
• The FIGI column can be left blank if there is no value. If entering a value, the response must be twelve (12) characters.
• The table must contain only whole number and no decimal values. To avoid problems with automatically rounded values:
  • Make sure columns with numerical values do not have decimals. Scroll through other columns to ensure that there are no stored decimal values in the table.
  • Excel rounds any decimals to the nearest whole number. When a cell is selected, the formula bar will display the decimal value at the top of the spread sheet, as depicted below. If the formula bar is showing decimal values, the validation will fail in EDGAR. To correct this, delete the value and manually type the rounded value.

• The Put/Call column can be left blank if there is no value. If entering a value, the only acceptable values are Put and Call and the first letter must be capitalized.
  • Sometimes cells in Excel contain a value even though the cells appear empty. This could result in errors if the contents of the cell are not cleared. To prevent this from occurring, be sure to clear the contents of any blank cell by right clicking on the empty cell and selecting Clear Contents. This will ensure that no data is being captured.
• The only acceptable values in the Investment Discretion column are SOLE, DFND, and OTR; all letters of the acronyms must be capitalized.
• The Other Managers column can be left blank if there is no value. If entering more than one number, use commas to separate the values (e.g. 1,10,23).
• The Sole, Shared, and None columns must have values for each entry. If there is no value, enter the numeral zero (0).
• If uncertain of the values for certain fields, please consult the 13F Frequently Asked Questions guide or contact the SEC Division of Investment Management Chief Counsel’s Office at (202) 551-6865.

Step 2—save the schema files

The schema files can be found at the Form 13F XML technical specification zip file on SEC.gov. Please note that the method for extracting the files to a folder varies depending on the zip software.

1. Select the link to the compressed folder at the bottom of the page to download and save the 13F XML Technical Specification file.

2. Click on Save and select the folder destination for the file. Select Save.

3. Open the folder, right click on the file, and select Extract All.

4. Browse to locate the saved schema file in the Extract Compressed (Zipped) Folders window and select Extract. A folder will be created that contains the 13F XML Technical Specification PDF, schema files—with  the .xsd file extension—and sample documents—with the .xml extension—as depicted below:

Step 3—enable the Developer tab in Excel

In order to map your table to XML, enable the Developer tab in Excel.

1. Select File > Options > Customize Ribbon. The Main Tabs option should be selected under the drop down listed under Customize Ribbon.

2. Select Developer to enable the tab as depicted below > OK.

Step 4—map the Excel file

The following instructions and corresponding images display the options in Excel 2010; please consult other resources if you have another version of Excel.

1. Select Developer from the main menu >  Source.

2. Select XML Maps, located at the bottom of the XML Source window.

3. The XML Maps window will appear. Click the Add button.

4. Locate the extracted files, select the eis_13FDocument file then select Open.

5. Click OK in the XML Maps window. The XML Source window will populate with the XML map data. Select ns1:informationTable to highlight the map.

6. Select the highlighted XML map by clicking the parent cell, ns1:informationTable. Hold the mouse down to drag the highlighted map to the A1 cell (Name of Issuer). The information table will be highlighted as depicted below.

If a window appears stating, “The data you are attempting to map contains formatting that is incompatible with the format specified in the worksheet,” then select Match Element Data Type.

Step 5—export the information table to an XML file

1. Select Developer >  Export.

2. Save the XML file to your desktop or desired location. The address bar on the top displays where the XML document will be exported. XML files can be saved in different locations by clicking different folders on the top, left-hand side of the application.

Your file name must have a file type extension of .xml and adhere to the following restrictions:

• 32 characters or less.
• All letters must be lowercase.
• The file must start with a letter.
• No spaces in the name.
• May have digits (0-9).
• May contain up to one period (.), one hyphen (-), one underscore (_).

Step 6—upload and validate the XML file in the electronic form 13F

1. Log in to the EDGAR Filing Website.
2. Depending on the type of filing you are making, select either File Form 13F or File Form 13F Confidential Treatment Request (Form 13F-CTR) under Online Forms.

3. Select the form type from the Type of Filing list and select the Next button.

The Continue with Saved Form option is applicable if the file has been saved from the electronic 13F form on the EDGAR filing website. The saved online form will be saved to the Downloads folder and will have an .eis file extension (e.g. 13F_Filer.eis).

4. Complete the filer information, cover, signature and summary pages.

5. In the Documents section, select Add Document > Choose Attachments > XML information table.

6. Click the box next to the File Name and choose Validate Document.

7. If your document has errors, click the red number to view errors that need to be corrected.

8. If you would like to save the form, select the Save button. You will be able to save the form to your computer so that you may use the Continue with a Saved Form option within the Type of Filing selection.

9. If the document has no errors and you are ready to submit your filing, click Submit.

10. For future reference, record the accession number to retrieve the form’s submission status at the EDGAR Filing Website.