Excel 2010 macro defining pivot table data source

  • Status Completed
  • Budget N/A
  • Total Bids 14

Project Description

I'm looking for a Excel wizard to develop a macro that will create a pivot table in excel 2010. Neither excel user is good with pivot tables so we some other approach may be worth discussing.

That said, I will attempt to explain the task below and some of the functional requirements of the excel macro dynamic pivot table.

So here goes:

The data. I have a data source in excel (financial metrics) from bloomberg professional, which are pulled into a specific format, the data period is the column header and the ticker is the row label.

The first major requirement is that a pivot table is created in a format that is "regression-ready" -- that is an X column and a Y column. The objective is to use the pivot table as the source for Excel's data analytic regression function.

The second requirement is that the pivot table's data source is dynamically set according to the number of tickers and number of periods. Example, the number of rows is based on the number of identifiers, i.e. tickers. and the number of columns is defined by the number of periods (i.e. 12 months, 10 years, etc). The pivot table needs to be able to have the data source modified and dynamically update with the new data.

I think I want a macro to launch a window that would allow me to simply highlight the number of time units and then the number of identifiers, then (I guess using a pivot table) a new tab would open where the data would automatically be populated into the regression-ready format. I would then be able to quickly run a regression analysis referencing the pivot table X and Y columns.

I would also want to have the ability to eliminate any row that has a ‘zero’ in the ‘X-plot’ column. I’m picturing in the window an ability to place y/n a check on ‘delete column if 0 in x plot?”

Get free quotes for a project like this
Completed by:

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online