Building One-Click Macro Program to Organize Corporate Executive Information

[alacey425]

ATTACHMENTS:
Word File (Project Directions)
Excel File (Project)


OBJECTIVE:
Create a one-click macro program that will organize various pieces of information from different worksheets about company executives into one organized table. The worksheets have different information on each person. This information needs to be located and then placed into the final table under the correct header column for each person. There must not be any duplicate names in the table.

HISTORY:
I have been developing this project for several months and I am pleased to say that with your help I will be nearly finished. I received initial help a few weeks ago with creating a macro that transposed information entered into “Sheet1” and organized it into the worksheet “Output”. I have included these worksheets if you need to reference. What I need your help with now is much more thorough.

TASKS One-Click MACRO COMPLETES:
1. Retrieve the main list of names from “Public Execs” along with their company (Ticker, e.g. SAVE US Equity) and position (CEO) and enter them into the “PUBLIC” worksheet under their proper column headers
a. Separate the executive’s name into First, Middle, Last, and Suffix columns
b. Separate the position into the Exec Position column
c. Enter the ticker under the Ticker column
i. Retrieve the full company name (e.g. Spirit Airlines Inc.), Phone Number, Website, and place them under the proper column headings
ii. Retrieve the address information and separate it into the correct columns. Ignore including the country.
d. Retrieve the compensation information if it is available from the “Compensation” worksheet
e. Under “Insiders”, if the person is listed as an insider, retrieve the “Amount” and put the value under the “# of Shares” heading. Also get the Filing Date.
i. To calculate “Value” multiply the # of Shares by the stock price (available under “Last Price” in the Public Companies worksheet)
f. Check to see if they are on their company’s board of directors (“Board”). If they are put “Y”, if not “N”. NOTE: if there are names under the “Board” worksheet that are not included from the “PublicExecs” list, do not add them just ignore them.

VERY IMPORTANT:
• Ignore the EMAIL column, this is something I need to work on separately.

• Above every header in the “EXAMPLE” worksheet I have labeled the worksheet where the information is located in.
• The format that the names appear in each worksheet is different for some sheets. For example: the name, (B BEN BALDANZA) under “Board” and (BALDANZA B BEN) under “Insiders”. Please take great care to assure that the information associated with each name in the table is correct.

• Using the Ticker (e.g. SAVE US Equity) is useful for navigating the sheets and getting information on the company.

• Please make it easy to add and subtract headings from the PUBLIC worksheet in the future. For example if I need to add a new column from “Insiders” or “Public Companies”, make it as easy as adding a column and heading and the column will populate the next time the program is run.

• For the sake of working with an actual set of data, I have copied and pasted only the ‘values’ of each cell for each worksheet, excluding the formulas. Normally all the data, except for the PUBLIC and PRIVATE worksheets, will be updating live from a Bloomberg terminal. Therefore for each worksheet the number of companies (number of columns) and length of information for each company (number of rows) will change frequently. It is very important that the program is flexible to calculate the current area of data for each worksheet each time it is run. The PUBLIC and PRIVATE worksheets are simply snapshots of the live feed.

• Not every information piece has a value. Replace any #N/A N/A or empty value with just a blank cell in the PUBLIC worksheet.


This job has also been posted in the Commercial Services forum

Please contact me with any questions:
My email is: alacey425@gmail.com
Phone: 717-579-4800

[alansidman]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.