SurprisedSeaLion-580

Q.Help!  I just discovered I need to file Form 13F with the SEC.  It requires CUSIPs but my PortfolioCenter database is missing many CUSIPs.  How can I find CUSIPs for the securities we currently hold and how can I get them into PortfolioCenter?  Please tell me you know a solution other than hours of manual entry.

A.Good news! There is a way to get CUSIPs into PortfolioCenter; it just takes a few steps.

The Situation

Investment managers “that exercise investment discretion over $100 million or more in Section 13(f) securities must file Form 13F” and Form 13F will only accept CUSIPs.   Only securities on the list count toward the $100 million total.  The list is updated quarterly (published only as a PDF; no electronic format) and the SEC will only accept the filing in XML format.  You have to file this form every quarter and the IRS estimates it will take you “only” 23.8 hours to prepare every filing.

A CUSIP is a unique numerical identifier assigned to each North American security.  Prior to approximately 2006, CUSIPs were not captured in the data downloads into PortfolioCenter.  Therefore, securities in PortfolioCenter may be missing the CUSIP if they were created prior to the time your broker(s) began sending CUSIP data and PortfolioCenter began reading it.

To check the status of your open securities, add the CUSIP field to the Master List of Securities and run the report on your active accounts set.

The Solution

Most brokers now send the CUSIP information in their PortfolioCenter datafeeds.  Here’s how to get PortfolioCenter to recognize and update the CUSIP field for existing securitites.

WARNING:  The steps below outline the general procedure, not the specific keystrokes needed to accomplish each task.  For specific step-by-step help, call the friendly folks at Schwab Performance Technologies 1-800-528-9595.

1.  Post and reconcile all the data you have downloaded.

2. Backup your PortfolioCenter database.

3.  If you skipped steps 1 & 2, go back and do them.  There is no “undo” feature.  If you make a mistake, your only recourse will be to retrieve your latest backup.  Be sure your latest backup is complete and recent.

4.  Open the PortfolioCenter Management Console and create a new dataset.  Let’s call it “TEMP” as opposed to your “REAL” database.

5. Back in PortfolioCenter, open the TEMP dataset.  Go to the Interfaces tabs and set the correct paths and settings for your broker.

6.  If necessary, rename the backup files for securities for the latest date.

7.  In the order of most accurate broker to least accurate broker, post the securities file in your temp dataset.  In my experience Schwab provides the most complete and accurate information, so I would post their files first.

8.  Still in the TEMP dataset, open the Securities Manager, All Securities View and export the all securities to a Excel,

9.  In your spreadsheet program, open the CSV file and edit it so that it includes ONLY the Symbol column and the CUSIP column.  Delete any cash funds and clean up any temporary symbols (e.g.RYP1Z for RYPRX).  The Symbols *must match your real data exactly.*   Save the edited file as CSV.

10.  In PortfolioCenter in your REAL database, open the Securities Data Manager All Securities View and select “Update from CSV”.   This may take a few moments depending on the size of your database.

11.  Review the changes (they will be highlighted in green) and then SAVE your changes.  Again, this may take a few moments.

12.  Use the Master List of Securities to check for any missing CUSIPs.  Add the CUSIP field to the report before running.  For best results, UNcheck the “Show Portfolio Positions” option and sort only by security type.

13.  When you’re done, go back to the Management Console and delete your TEMP dataset.

14. While you’re in the Management console, reindex your database.

Need help?