Change Formatting of Data

[schwang]

Hey all!

I am processing data downloaded from a Bloomberg terminal for over 8000 firms, but I am experiencing difficulty. The data set is in the current format:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]AAPL[/TD]
[TD]BAC[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]0.0430[/TD]
[TD]-0.0131[/TD]
[/TR]
[TR]
[TD]1/2/2018[/TD]
[TD]4.6958[/TD]
[TD]0.1438[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]0.8888[/TD]
[TD]-0.1045[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[TD]1.0708[/TD]
[TD]-0.4836[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[TD]2.5748[/TD]
[TD]-0.3809[/TD]
[/TR]
</tbody>[/TABLE]


I need the data to be in the following format:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Ticker[/TD]
[TD]RET[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]AAPL[/TD]
[TD]0.0430[/TD]
[/TR]
[TR]
[TD]1/2/2018[/TD]
[TD]AAPL[/TD]
[TD]4.6958[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]AAPL[/TD]
[TD]0.8888[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[TD]AAPL[/TD]
[TD]1.0708[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[TD]AAPL[/TD]
[TD]2.5748[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]BAC[/TD]
[TD]-0.0131[/TD]
[/TR]
[TR]
[TD]1/2/2018[/TD]
[TD]BAC[/TD]
[TD]0.1438[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]BAC[/TD]
[TD]-0.1045[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[TD]BAC[/TD]
[TD]-0.4836[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[TD]BAC[/TD]
[TD]-0.3809[/TD]
[/TR]
</tbody>[/TABLE]

I'm sure there's a way to do this using OFFSET and other tools but I can't figure it out. If need be, VBA is fine too, but formulas would be preferred if at all possible. Thanks in advance!

 

[sandy666]

using PowerQuery (Get&Transform):

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]AAPL[/td][td=bgcolor:#5B9BD5]BAC[/td][td][/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Attribute[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2018​

[/td][td=bgcolor:#DDEBF7]

0.043​

[/td][td=bgcolor:#DDEBF7]

-0.0131​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/01/2018​

[/td][td=bgcolor:#E2EFDA]AAPL[/td][td=bgcolor:#E2EFDA]

0.043​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/02/2018​

[/td][td]

4.6958​

[/td][td]

0.1438​

[/td][td][/td][td]

01/01/2018​

[/td][td]BAC[/td][td]

-0.0131​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/03/2018​

[/td][td=bgcolor:#DDEBF7]

0.8888​

[/td][td=bgcolor:#DDEBF7]

-0.1045​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/02/2018​

[/td][td=bgcolor:#E2EFDA]AAPL[/td][td=bgcolor:#E2EFDA]

4.6958​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/04/2018​

[/td][td]

1.0708​

[/td][td]

-0.4836​

[/td][td][/td][td]

01/02/2018​

[/td][td]BAC[/td][td]

0.1438​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/05/2018​

[/td][td=bgcolor:#DDEBF7]

2.5748​

[/td][td=bgcolor:#DDEBF7]

-0.3809​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/03/2018​

[/td][td=bgcolor:#E2EFDA]AAPL[/td][td=bgcolor:#E2EFDA]

0.8888​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]

01/03/2018​

[/td][td]BAC[/td][td]

-0.1045​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

01/04/2018​

[/td][td=bgcolor:#E2EFDA]AAPL[/td][td=bgcolor:#E2EFDA]

1.0708​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]

01/04/2018​

[/td][td]BAC[/td][td]

-0.4836​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

01/05/2018​

[/td][td=bgcolor:#E2EFDA]AAPL[/td][td=bgcolor:#E2EFDA]

2.5748​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]

01/05/2018​

[/td][td]BAC[/td][td]

-0.3809​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"AAPL", type number}, {"BAC", type number}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"Date"}, "Attribute", "Value")
in
    Unpivot[/SIZE]

 

[schwang]

Oh wow, that was easy. Thanks so much!!

 

[Rick Rothstein]

For those reading this thread who do not use PowerQuery, here is a macro solution that should work (set WS1 to the data sheet and WS2 to the output sheet)...

[table="width: 500"]
[tr]
	[td]Sub RearrangeData()
  Dim Col As Range, Rw As Long
  Dim WS1 As Worksheet, WS2 As Worksheet
  Dim Dates As Variant, Ticker As Variant, RET As Variant
  Set WS1 = Sheets("[B][COLOR="#FF0000"]Sheet1[/COLOR][/B]")
  Set WS2 = Sheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]")
  Dates = WS1.Range("A2", WS1.Cells(Rows.Count, "A").End(xlUp))
  Application.ScreenUpdating = False
  WS2.Range("A1:C1") = Array("Date", "Ticker", "RET")
  For Each Col In WS1.Range("B2", WS1.Cells(2, Columns.Count).End(xlToLeft)).Columns
    Rw = WS2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    WS2.Cells(Rw, "A").Resize(UBound(Dates)) = Dates
    WS2.Cells(Rw, "B").Resize(UBound(Dates)) = WS1.Cells(1, Col.Column).Value
    WS2.Cells(Rw, "C").Resize(UBound(Dates)) = WS1.Cells(2, Col.Column).Resize(UBound(Dates)).Value
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[sandy666]

Oh wow, that was easy. Thanks so much!!

You are welcome

Have a nice day