SOLVED

Transposing problem

Deleted
Not applicable

I have a dataset of more than 2000 mutual funds, for these funds I have data over time for different characteristics like return and certain industry weights in their portfolio.

 

The problem here is, is that per mutual fund I have one row containing all the data. The data for the particular characteristic per time period is divided per column. And the dates of the different characteristics is only in the column header, so not in a seperate cell or anything. To be able to analyse the data I need it in a panel data format where every mutual fund has got the seperate time periods with all the data standing besides it, and that for every fund in my sample. 

 

I do not think I can use the transpose function because of the sheer number of characteristics that need transposing. Do you have any suggestions to solve this problem?

 

Thanks in advance!

In the attachment you can find the data for one fund, so you can see my problem.

11 Replies

Hi Jur

Try this :

 

1 on your worksheet click in order (not all together) Alt D P,  it will open the PivotTable Wizard

2 on the window choose Multiple consolidations ranges 

on the window choose I will Create the page fields 

4 choose the range you are interested (Sheet1!$A$1:$BTA$2) click Add then Next

5 choose New worksheet

6 on the new worksheet with the pivot table level only Count of Value (see the file)

 

Count of Value

727

 

7 double click on the number it will create a new worksheet with data transposed to kind of database table records 

 

What do you think about that? 

 

it's working great when you have a few worksheets transpose to database 

 

 

Thanks for your reply, I have a more precise description of the problem now and I am curious if this works for this particular problem as well:

 

Hello everybody,

I am having problems transposing data in Excel. I have a large dataset of around 2000 mutual funds, for these funds I have cross sectional data and time series data for four different variables: Returns, concentration, active share and fund size.
Per row there is one fund with all the accompanying data in the columns right from that cell for all the time series data and other variables.

The problem here is that I cannot find a solution for how to transpose the dataset to a panel format. In the attachment you find an example of the data for three mutual funds and what the desired sollution is to the problem.
That is the best way I can describe it to you I figured. 

Thanks in advance,
All pointers/tips/tricks are very welcome!

Hoekstraat

best response
Solution

Hi Jur,

 

That could be done with Power Query (aka Get&Transform). Convert source to the Excel table, with one query form left side with chronological data, another query for static one, append second to first and generate final table.

 

Please see attached. That's done without cosmetic, consider as prototype.

The example you send with the reply is exactly what I mean, thank you so much.
Can you maybe be more specific in which steps you took to get to this final result?
If you have a step by step walk-through I can apply this to the full dataset.

Thanks in advance

Jur, I'm not sure if know at least something about Power Query, let start from scratch the. First be sure you have it. If you are on Excel 2010 or 2013 you shall download from here https://www.microsoft.com/en-us/download/details.aspx?id=39379 add-in and install it. Power Query group will appear in ribbon.

 

For Excel 2016 it's built-in and called Get&Transform, see here

image.png

Menu could be slightly different from version to version.

 

Second, you have to convert you data into Excel table https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e49...

 

Since you have empty columns in the middle of your data you shall do that fully manually - select entire range with your data, Ctrl+T, check box My table has headers, Ok. That is one time operation, with adding new data table will be expanding automatically. Stay on any cell inside table. Here you may rename it from ribbon Table Tools->Design

image.png

by default it'll be Table1 or like.

 

Next click on From Table/Range button

image.png

query editor opens. Do nothing so far but click Close and Load to

image.png

select Only Create Connection

image.png

and save workbook. Now you may return to your query for editing at any time. What to do with it in next message(s).

To continue with query editing in excel ribbon click on

image.png

in opened right pane right click on query name and select Edit. You are in query editor now. On the right you'll see two generated steps, delete second one

image.png

select all columns like in Excel (first column, Shift, last column) and in editor ribbon click on

image.png

and replace on null

image.png

Now select only first Name column and

image.png

Text filter second column only with records needed for the left part of your resulting table

image.png

Now we shall split text and dates in your second column. Let remove the text after the date first. Select column and doimage.png

and replace USD on nothing (don't forget the space before USD)

image.png

Select second column and split it

image.png

by right space

image.png

Most probably third column will automatically changed on Date type, you will see one more step

image.png

If not - select it and Home->Data type->date.

Now select all columns and

image.png

After that select second column and drag it to the left on first position

image.png

Keep it selected and pivot

image.png

without aggregation on Value

image.png

Rename columns where necessary (double click on header and change the text).

That's all with this query, close the editor and save workbook.

Let continue with static information. Open in Excel right pane with queries, right click on your first query and Duplicate. Rename it (let say on Static) and Close and Load to -> Only create connection.

 

Open it again on editing and remove all steps from the bottom one by one (click on cross at the left on step name) keeping Filtering Rows as the last available step

image.png

Stay on that step, expand formula bar and within it change manually every "Text.Start..." on "not Text.Start..." and every "or" on "and", click Enter after that.

Final formula shall be as

image.png

collapse formula bar.

Select Attribute column (second one) and pivot it with Value column without aggregation.

That's all with this query.

 

Now return back to the editing of first query. After you opened it when Home->Append Queries

image.png

and select second query to append

image.png

Now sort ascending Name column and after that sort ascending Date column

image.png

Close query editor and keep changes.

Now land results into the excel sheet. Open right pane and right click on first query name, select Load to, here is Table and location where you'd like to keep it

image.png

Right click on any cell within the table and select External table properties

image.png

within it deselect Adjust column width.

Now you may add bit cosmetic to your table - change design and format of columns / headers.

With changing of source data click Refresh in right click menu of the table or in Data section of ribbon.

 

File with above steps is attached.

 

As a comment, resulting data is bit incorrect. You have

- !DIV/0 errors within the source data,

- repeating column names (e.g. Return 5-2012 and some else repeated two times). It shall be corrected in source (all names are to be unique) or processed additionally within the query.

- empty columns in the middle;

- perhaps something else

 

But that better test on copy of real data.

I am working on transposing the data right nog within get&transform, but when filtering in the query editor I do not have the ''advanced filter'' option that you mention. Is that just because of a later update?

Is there another option i could use maybe?

Did I understand correctly you are on Excel 2016? Advanced mode for row filtering was introduced in July 2016 https://blogs.office.com/en-us/2016/07/25/july-2016-updates-for-get-transform-in-excel-2016-and-the-... for Office 365 subscribers on Excel 2016 and for power query add-in. I believe it shall be now for all versions. That only could be bit different interface.

 

Anyway, you always may edit the expression in formula bar or in Advanced editor taking as a sample the one in my file.

I updated me office now, so hopefully I can enter the advanced filters now.

Thanks!

1 best response

Accepted Solutions
best response
Solution

Hi Jur,

 

That could be done with Power Query (aka Get&Transform). Convert source to the Excel table, with one query form left side with chronological data, another query for static one, append second to first and generate final table.

 

Please see attached. That's done without cosmetic, consider as prototype.

View solution in original post