How to create hyperlinks to SEC filings in an Excel report
In a blog post from last year, we enumerated the ways in which Knowledge Mosaic lets you share your search results with a colleague –including email, batch download, and Excel report generation (to name only three of the seven). Today, we offer a trick that makes the Excel report even more valuable. By following these simple steps, you can create hyperlinks to documents in the spreadsheet you generate from the Knowledge Mosaic SEC Filings page.
Step 1. Create your Excel report. This option is available once you’ve run your search and have a set of results. Look for the link in the upper right hand corner of your screen, as in this screenshot:
Step 2. Insert three new columns into the Excel spreadsheet: one to the immediate left of the accession number column (Column H), another to the immediate right, and a third anywhere you like — that’s the one where your URL will eventually appear.
Step 3. Starting with your first row of filing results (row 18), enter in the empty column just to the left of the Accession No. column this URL stub:
(It’s best to paste the URL stub as text only rather than as a hyperlink.) Then copy and paste that stub in the subsequent rows, all the way down the column.
Step 4. Deploy the Excel “concatenation” formula. To do so, first click into the cell to the immediate right of the first accession number column, then access the Excel formula bar:
Once the formula dialog box has opened, select the “CONCATENATE” function (or type it in as text), and hit “OK.”
Step 5. Click into the cells to designate them for concatenation: first the URL stub cell (“Text1”), then the Accession No. cell (“Text2”). Then hit “OK.”
Step 6. Copy the new contents of that cell — i.e., the formula result, a full URL created by concatenating the URL stub and accession number — on down the column.
Step 7. Convert the URL to a hyperlink. In whatever column you created as a placeholder for the hyperlink, click into the cell corresponding to the first result. Deploy the formula bar and select “HYPERLINK.” In the function arguments dialog box, designate the link location by clicking on the cell containing the full URL. (Optionally, you can also choose a “friendly name” that will contain the hyperlink — it could be “filing,” or “link,” or “Fred,” or “Ethel” — whatever works for you.) Click OK.
Finally, copy and paste the cell contents down the rest of the column. And don’t forget to save your document!