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:

	AAPL	BAC
1/1/2018	0.0430	-0.0131
1/2/2018	4.6958	0.1438
1/3/2018	0.8888	-0.1045
1/4/2018	1.0708	-0.4836
1/5/2018	2.5748	-0.3809

<tbody>
</tbody>

I need the data to be in the following format:

Date	Ticker	RET
1/1/2018	AAPL	0.0430
1/2/2018	AAPL	4.6958
1/3/2018	AAPL	0.8888
1/4/2018	AAPL	1.0708
1/5/2018	AAPL	2.5748
1/1/2018	BAC	-0.0131
1/2/2018	BAC	0.1438
1/3/2018	BAC	-0.1045
1/4/2018	BAC	-0.4836
1/5/2018	BAC	-0.3809

<tbody>
</tbody>

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):

Date	AAPL	BAC		Date	Attribute	Value
01/01/2018​	0.043​	-0.0131​		01/01/2018​	AAPL	0.043​
01/02/2018​	4.6958​	0.1438​		01/01/2018​	BAC	-0.0131​
01/03/2018​	0.8888​	-0.1045​		01/02/2018​	AAPL	4.6958​
01/04/2018​	1.0708​	-0.4836​		01/02/2018​	BAC	0.1438​
01/05/2018​	2.5748​	-0.3809​		01/03/2018​	AAPL	0.8888​
				01/03/2018​	BAC	-0.1045​
				01/04/2018​	AAPL	1.0708​
				01/04/2018​	BAC	-0.4836​
				01/05/2018​	AAPL	2.5748​
				01/05/2018​	BAC	-0.3809​

[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