Creating a SEC 13D filing table with web query

[mm1099]

Hello everyone,

I have very little Excel experience, but I would like to create a spreadsheet that, I imagine, will involve macros and programming (of which I know nothing about).

Here's my goal: I'm interested in importing Securities Exchange Commission 13D/G filings from the internet into a spreadsheet in order to know whenever a hedge fund or other investor files a 13D or 13G.

I have found a website that provides live updates on 13D/G filings called WhaleWisdom (http://whalewisdom.com/schedule13d). I was able to copy some code I found online and I made a simple web query that imports the most current table from WhaleWisdom into Excel (I got the code from here: http://www.teachexcel.com/free-excel...port-data.html).

My first question: The columns on WhaleWisdom's table, as you can see on the website, contain data for: Type, File Ownership History, Filed By, Subject Company, Ticker, Date, Reason. When I run the web query, it imports the data using those categoies, in that order. However, I want to change both the order and the categories, but keep the data. So, I want to have the following columns, in the following order, titled as follows: "Date", "Type", "Total Reported Shares", "Filed By", "Subject Company", "Ticker". I don't want columns for "Ownership File" or "Reason." How could I do that? What would I need to do to modify the web query macro?

My second question: Let's say I have the WhaleWisdom table in Sheet1 in Excel. I would like to be able automatically search through that table and update tables in Sheet2 with specific information.

Specifically, on Sheet2, I want to have a table with the following columns: "Name", "Date", "13D", "13D/A", "13G", "Subject Company." The "Name" Column would be a set list of hedge funds that I determine ahead of time. So, let's say Third Point LLC (a hedge fund) files a 13D; the WhaleWisdom table in Sheet1 would show this filing. On Sheet2, Third Point would be in the name column ahead of time. I would want code that would search Sheet1, find the Third Point entries, and then fill in Sheet2 with a check mark in the 13D, 13D/A, 13G columns (depending on what Third Point filed), and fill in the correct Subject Company. What kind of code could do these functions?

Thank you so much in advance. I'm sure this is a tall order for a help forum, and I'm trying to teach myself through tutorials, but any guidance, ideas, or sample code, would be very, very helpful.

Best,
mm1099


I would like to be able to automatically search through that table, on Sheet1, for specific names in the "Filed By" and "Subject Company" columns and then

[ShosMeister]

Based on just the link for the web query code you provided, it's not clear that you are saving the data from each query. If there are say 10 new records when you run it, it appears to start putting the data into $A$1. So, if you run it again, what happens to the old 10 rows? Are you saving them off somewhere or did you change the code to move the insertion point of the new data?

[mm1099]

Ah, that is a good point. I didn't realize that the code would just override the previous data. How could I change the code to move the insertion point?

Thanks for noticing that!

[ShosMeister]

That depends on if you want to keep the raw data or not. If you don't need anything other than the columns you indicated above, then I'd just import, parse over to your format, then remove the original raw data. Your parsed data would be your running source.

[mm1099]

Right, I wouldn't want to keep the raw data (with WhaleWisdom's column categories). I want the data parsed into the appropriate column titles that I indicated in my first question above; that would be my Sheet1. Any suggestions on how to proceed with the code?

[ShosMeister]

Attach a copy of whatever you have now showing the original column mapping to yours and the format for the data and the summary and I'll see what I can come up with.

[mm1099]

ShosMeister, my apologies for the delay; I didn't have my work computer available to post the Excel file last night.

Attached is the excel file I'm working out of. When you run the macro, you'll see data pulled from the WhaleWisdom website on the sheet called "original data." The sheet called "My Sheet1" has the names of the columns I want (as described in my above post) with comments explaining what from the original data columns goes to the "My Sheet1" columns. "My Sheet 2" also has the columns I want, described above too, with comments explaining how it relates to the original data sheet.

As you pointed out, I would not want the previous data to be overidden each time I run the macro, but I don't need the raw data as it appears in WhaleWisdom's columns.

Thank you so much for offering to help with this; I really appreciate it.

[ShosMeister]

I'm swamped right now at work and school but I'll try to look at it in the next couple of days.

[mm1099]

Great, thanks so much. And yes, as your schedule allows.

[mm1099]

Does anyone have any thoughts/suggestions for this project?

[ShosMeister]

Working on it. Sorry for the delay.

[mm1099]

Fantastic. No worries about the delay, ShosMeister. I appreciate it!

[ShosMeister]

Here's a start. Take a look at the code as well as I have some comments in there.

Big question now is how you want to manage the last sheet. Could be done with formulas but would require some other things be in place first and some minor rules to follow. Advantage is it would always be updated.

Or could be via VBA but you'd have to run a macro (or click a button) whenever you wanted an updated view.

[mm1099]

Thanks for uploading a spreadsheet, ShosMeister. I'll take a look as soon as I have some downtime at work (I have a mac at home, so no macro use).

[mm1099]

Here's a start. Take a look at the code as well as I have some comments in there.

Big question now is how you want to manage the last sheet. Could be done with formulas but would require some other things be in place first and some minor rules to follow. Advantage is it would always be updated.

Or could be via VBA but you'd have to run a macro (or click a button) whenever you wanted an updated view.

I looked into the spreadsheet. It looks great! Just a few comments/questions I have at this point:
1. On the "Query" worksheet, I don't need any of the items that are in cells A3-A74. Would it be possible to remove those items so that the item currently in A75 (Choose day to view: ) appears at the top of the sheet, in A3? ( basically I want everything in A75 and below to appear at the top)

2. I'm guessing you haven't coded the spreadsheet to rearrange the data from the Query to fill in "My Sheet1" and "My Sheet2." To your above question, having to click a button is fine for those sheets (its actually more useful for my purposes than to have it be automatically updating).

I looked through the code (which I don't really understand), but it seems to be good! This is amazing progress on this. Thanks so much.

[ShosMeister]

1. On the "Query" worksheet, I don't need any of the items that are in cells A3-A74. Would it be possible to remove those items so that the item currently in A75 (Choose day to view: ) appears at the top of the sheet, in A3? ( basically I want everything in A75 and below to appear at the top)

Not really. Notice the comment in the macro indicating that you do not get the date for the query unless you get the entire page. But, that page is just to pull in the data. What is copied over to your Original Data sheet should be what you want.

2. I'm guessing you haven't coded the spreadsheet to rearrange the data from the Query to fill in "My Sheet1" and "My Sheet2." To your above question, having to click a button is fine for those sheets (its actually more useful for my purposes than to have it be automatically updating).

Will mean another macro to do that rather than an in-line formula. No problem either way.

I looked through the code (which I don't really understand), but it seems to be good! This is amazing progress on this. Thanks so much.

Please look again and I'll try to answer any questions. It's important that you understand what it's doing for when you have to edit it - which I'm sure you will. I tried to comment it out a bit to help.

[Kyle123]

Another way:

Please Login or Register  to view this content.

[Kyle123]

Here's with comments and re-structured slightly - should be easier to understand

Please Login or Register  to view this content.