Download and Clean data from Bridge

Background: 

We, IS Careers Staffs, work under Reid Grawe who is career advisor for all the IS students in BYU. The main goal that we have is to help all the IS students to have a job when they graduate. In order to achieve the goal, we research companies, keep track of student placements, providing internship/job information, and so on. One of the weekly tasks is sending “Friday Forecast” which is a weekly newsletter with Reid’s message, upcoming events information, and job/internship information. As an Excel attachment, we generate two different reports from bridge. One report has information of all the events of companies, and another report has information of all Job/Internship/part-time position. We combine two different reports into one Excel sheet with four tabs (Events, Job, and Internship/Part-time). We create this weekly report every Friday and send it to all the IS students with the newsletter.

Problems: 

One of the main problems with creating the weekly report is that it takes time every week. It takes about an hour to just create the weekly report for the following reasons.

1. We have to make the report readable. The reports generated by Bridge system is raw data which is not easy to read for the viewer
2. We have to clean the data. We get data which we don't need even though we use pre-set SQL code of Bridge’s report system to generate the reports. We have to manually check more than 2500 job/internship position to filter if they are related to IS major.
3. We have to organize the folder to save the file each week. For the record purpose, we have to make directory each year, each month, and each week to save the weekly report in the certain file with the HTML of the Friday Forecast and the week’s other attachments.

Solution: 

As requested, I developed a VBA macros to automate the most of the work and reduce the amount of manual work and time. The deliverable project currently includes the following features:

1. Automated reports generation and download from the Bridge system.
• For security concern, it asks you to type your username and password each time.
• You will wait for 5 to 50 seconds until the report is completed because of the Bridge reporting system. The VBA code will resume when the report is complete.
• Message box pops out when the process is done
2. Automated organizing, filtering, and cleaning data.
• When the macro runs, the report is well-formatted and readable.
• The report shows only IS-related Information of next two weeks from coming Friday instead of showing all the raw data.
3. Automated combining two different files into one Excel sheet with four tabs.
4. Automated creating directory based on the day you create the report and saving files.
• Example: C:\Users\msm-advisorass\Dropbox\IS Career Center\Friday Forecast\Weekly Reports\2015\March\Mar 27

Expanded detail of individual applications in the macro can be found in the Implementation Documentation.

Link:

• http://files.gove.net/shares/files/15w/shingo2/S._Osaka_FF_Scraper.xlsm
• http://files.gove.net/shares/files/15w/shingo2/S._Osaka_Project_Report.pdf