Web Scraping using Excel VBA macros
Budget: $250 – $750 USD
Hello I am looking for an Excel/VBA solution to scrape public corporate records from the Secretary of State (“SOS”) of Massachusetts and then save the appropriate PDF documents in a folder. I am calling this project “Corporate Detective”.
I am very familiar with Excel/VBA but do not have much experience scraping. This file will be distributed to other users who are not very technical, so I will insist on an Excel/VBA solution using Internet Explorer even though I understand Python/Chrome is easier/better/faster for scraping.
The Excel file is attached. I am looking for two functionalities using the SOS site which is:
https://corp.sec.state.ma.us/corpweb/CorpSearch/CorpSearch.aspx
First Function is to search a name on the SOS site with a wildcard and capture all possible names on a Results sheet to show the User. Sometimes a name will be searched with zero result, sometimes there will be many. I like reporting results to the spreadsheet using Named Ranges so we can later move around columns, insert columns etc. I also like to show user inputs as a blue color, and macro results as black.
Example “Disney” (begins with) yields 20 possible name results on the GuessResults sheet.
Second Function is the User chooses a specific name or a list of specific names on the Names sheet, and the results of documents as defined on the Dash sheet get reported to the SearchResults sheet, and all the pdf documents gets saved to the SearchResults folder in the same directory as the Excel file.
If this goes well I would also like in the future to add other major states like NY, CA, TX, FL so I also have a column for State, although for now they will all be MA for Massachusetts.
I am very familiar with Excel/VBA but do not have much experience scraping. This file will be distributed to other users who are not very technical, so I will insist on an Excel/VBA solution using Internet Explorer even though I understand Python/Chrome is easier/better/faster for scraping.
The Excel file is attached. I am looking for two functionalities using the SOS site which is:
https://corp.sec.state.ma.us/corpweb/CorpSearch/CorpSearch.aspx
First Function is to search a name on the SOS site with a wildcard and capture all possible names on a Results sheet to show the User. Sometimes a name will be searched with zero result, sometimes there will be many. I like reporting results to the spreadsheet using Named Ranges so we can later move around columns, insert columns etc. I also like to show user inputs as a blue color, and macro results as black.
Example “Disney” (begins with) yields 20 possible name results on the GuessResults sheet.
Second Function is the User chooses a specific name or a list of specific names on the Names sheet, and the results of documents as defined on the Dash sheet get reported to the SearchResults sheet, and all the pdf documents gets saved to the SearchResults folder in the same directory as the Excel file.
If this goes well I would also like in the future to add other major states like NY, CA, TX, FL so I also have a column for State, although for now they will all be MA for Massachusetts.