Brief Instructions for data collection: (Two companies’ results are shown as examples in the attached spreadsheet. Company codes are 10017 and 25178.)
1. Go to website: [url removed, login to view]
2. In the attached spreadsheet find the 5-digit “company code” from the third column for the first company, which is labeled as “CODE”. Input the company code in the starred input slot on the right hand side of the page. Click on the button “Find a Company”.
3. Double check if the company name is consistent with the column “CONAME” corresponding to the “code” chosen at the step 2. (If you cannot find the company, leave the whole row blank and move to the next row.)
4. On the middle-left hand side of this page, click on the link under Available Information that is labeled as “Closed Complaints”. On the new page, we need information from three different links. The three links are: “Closed Complaint Counts By State”; “Closed Complaint Counts By Code”; and “Closed Complaint Ratio Report”. Please input them in the attached Excel table Column E to MO. More specifically:
a) Click the “Closed Complaint Counts By State” link and go to the “Closed Complaint Counts By State Report:” page. Choose the year 2008 and then click the button labeled as “Create Report”. Input all the number in the “the Complaint Counts By State Report” into the attached Excel Table Spreadsheet “year2008” Column “E-BI”. This will be repeated for years 2009, 2010, and 2011. There is a separate page in the excel worksheet for each year of data.
b) Go back to the “Closed Consumer Complaint Reports:” page. Click the link “Closed Complaint Counts by Code”. In the third box, for which year would you like to see complaints” choose 2008. Input all the numbers in the “Number of Complaints by Coverage Type”, Reasons why Complaints were Submitted, and Final Decisions regarding complaints into the attached Excel Table Spreadsheet “year2008*” Column “BK-MC”. (Table “NUMBER OF COMPLAINTS BY COVERAGE TYPE” in Column “BK- HS”; Table “REASONS WHY COMPLAINTS WERE SUBMITTED” in Column “HU- LL”; Table “FINAL DECISIONS REGARDING COMPLAINTS” in Column “LN- MC”). Repeat this step for years 2009, 2010, 2011. Note that several columns are designed to having no number at all, i.e., BK, BL, CM, DC, DZ, ES, GM, GZ, HT, HU, HV, IO, JM, KM, LM and LN.
c) Go back to the closed consumer complaint reports. Click the link “Closed Complaint Ratio Report”. At the “Closed Complaint Ratio Report” page, choose all categories one by one “For which insurance type would you like to see complaints?” First choose commercial auto (if available). For which year - choose 2008. Click - create report. On the new page there will be box with a single red number in it. Put that number in the attached Excel table Column ME. Go back and repeat this process for each type of insurance (commercial liability, commercial property, etc.). Put this data in the subsequent columns after MF, MG, etc. Also, many firms will have missing values for some of these variables (e.g., if “commercial auto” is missing, please leave column ME of that row blank.). Repeat this process for years 2009 and 2010. Note that this data is not available for year 2011.
5. The database used to contain the same information for years 2005-2007. If that data becomes available please pull it as well. This additional work would be compensated as the same rate.
38 freelancers are bidding on average $398 for this job
Hi I've go thru your project description and did a quick study on the website for the data source. I can write a script to crawl and deliver the 2008-2011 data to you in ~4days.