Calculating ratios in panel data

[Michael Craig]

I searched Stata help and this forum but found no solution. I want to calculate some simple ratios on panel data like Book Leverage = Liabilities (LT) / Total Assets (AT) getting this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str61 Description str7 ID_S float qdate long(LT AT) float Book_Lev long(EBITDA SALES PPE)
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 203 146433000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 200 146229000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 198 139348000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 197 138153000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 199 136735000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 196 135862000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 194 131956000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 195 127187000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 193 119456000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 192 115449000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 189 114407000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 188 114190000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 190 113145000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 184 111548000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 191 110310000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 186 109690000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 185 109276000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 187 106606000         . . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 220         . 375827000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 221         . 374019000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 223         . 373909000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 222         . 371411000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 218         . 347308000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 219         . 345331000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 217         . 336124000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 216         . 333909000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 214         . 323398000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 212         . 321771000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 213         . 319092000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 215         . 318711000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 210         . 308973000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 211         . 301729000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 209         . 274958000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 208         . 264919000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 207         . 247293000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 206         . 228818000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 205         . 218175000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 204         . 213255000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 202         . 195734000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 203         . 195145000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 201         . 192960000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 200         . 188121000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 198         . 176784000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 197         . 174568000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 199         . 174165000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 196         . 172807000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 194         . 170631000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 195         . 164575000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 193         . 154627000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 192         . 149301000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 189         . 143284000 . . . .
end
format %tq qdate

The data is later to be used for panel regression on this quarterly dataset.

Thanks for sharing your thoughts.

[Clyde Schechter]

This data organization is not really amenable to this kind of calculation. You need to have total assets and total liabilities in separate variables, properly paired up by qdate. So there are a number of steps:

split Description, parse("-") gen(v)
rename v1 firm
rename v2 varname
replace firm = trim(itrim(firm))
replace varname = lower(strtoname(varname))

assert missing(LT) if !missing(AT)
egen v = rowmin(LT AT)
drop LT AT Description

reshape wide v, i(firm qdate) j(varname) string
rename v_* *

gen book_leverage = total_liabilities/total_assets

[Nick Cox]

I am going to assume that you want this done for each distinct quarterly date as well as each firm. This isn't explained in #1. You can do this with the present data layout, but as Clyde implies the result is awkward.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str61 Description str7 ID_S float qdate long(LT AT) float Book_Lev long(EBITDA SALES PPE)
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 203 146433000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 200 146229000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 198 139348000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 197 138153000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 199 136735000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 196 135862000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 194 131956000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 195 127187000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 193 119456000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 192 115449000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 189 114407000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 188 114190000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 190 113145000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 184 111548000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 191 110310000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 186 109690000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 185 109276000         . . . . .
"VOLKSWAGEN AG - TOTAL LIABILITIES" "D905009" 187 106606000         . . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 220         . 375827000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 221         . 374019000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 223         . 373909000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 222         . 371411000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 218         . 347308000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 219         . 345331000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 217         . 336124000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 216         . 333909000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 214         . 323398000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 212         . 321771000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 213         . 319092000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 215         . 318711000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 210         . 308973000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 211         . 301729000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 209         . 274958000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 208         . 264919000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 207         . 247293000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 206         . 228818000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 205         . 218175000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 204         . 213255000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 202         . 195734000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 203         . 195145000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 201         . 192960000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 200         . 188121000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 198         . 176784000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 197         . 174568000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 199         . 174165000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 196         . 172807000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 194         . 170631000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 195         . 164575000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 193         . 154627000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 192         . 149301000 . . . .
"VOLKSWAGEN AG - TOTAL ASSETS"      "D905009" 189         . 143284000 . . . .
end
format %tq qdate

bysort ID_S qdate (Description) : gen wanted = LT[2] / AT[1]

list qdate Description LT AT wanted if qdate < 195, sepby(qdate)

     +-------------------------------------------------------------------------------+
     |  qdate                         Description          LT          AT     wanted |
     |-------------------------------------------------------------------------------|
  1. | 2006q1   VOLKSWAGEN AG - TOTAL LIABILITIES   111548000           .          . |
     |-------------------------------------------------------------------------------|
  2. | 2006q2   VOLKSWAGEN AG - TOTAL LIABILITIES   109276000           .          . |
     |-------------------------------------------------------------------------------|
  3. | 2006q3   VOLKSWAGEN AG - TOTAL LIABILITIES   109690000           .          . |
     |-------------------------------------------------------------------------------|
  4. | 2006q4   VOLKSWAGEN AG - TOTAL LIABILITIES   106606000           .          . |
     |-------------------------------------------------------------------------------|
  5. | 2007q1   VOLKSWAGEN AG - TOTAL LIABILITIES   114190000           .          . |
     |-------------------------------------------------------------------------------|
  6. | 2007q2        VOLKSWAGEN AG - TOTAL ASSETS           .   143284000   .7984632 |
  7. | 2007q2   VOLKSWAGEN AG - TOTAL LIABILITIES   114407000           .   .7984632 |
     |-------------------------------------------------------------------------------|
  8. | 2007q3   VOLKSWAGEN AG - TOTAL LIABILITIES   113145000           .          . |
     |-------------------------------------------------------------------------------|
  9. | 2007q4   VOLKSWAGEN AG - TOTAL LIABILITIES   110310000           .          . |
     |-------------------------------------------------------------------------------|
 10. | 2008q1        VOLKSWAGEN AG - TOTAL ASSETS           .   149301000   .7732634 |
 11. | 2008q1   VOLKSWAGEN AG - TOTAL LIABILITIES   115449000           .   .7732634 |
     |-------------------------------------------------------------------------------|
 12. | 2008q2        VOLKSWAGEN AG - TOTAL ASSETS           .   154627000    .772543 |
 13. | 2008q2   VOLKSWAGEN AG - TOTAL LIABILITIES   119456000           .    .772543 |
     |-------------------------------------------------------------------------------|
 14. | 2008q3        VOLKSWAGEN AG - TOTAL ASSETS           .   170631000   .7733413 |
 15. | 2008q3   VOLKSWAGEN AG - TOTAL LIABILITIES   131956000           .   .7733413 |
     +-------------------------------------------------------------------------------+

I don't know what you searched in terms of questions, but as in many intermediate problems in Stata this answer leans heavily on by:

[Michael Craig]

I am not sure if reshaping the data is really needed, as the variables are already separated (the observations in the descriptions are probably misleading).

Here are all the main variables that I need for several ratio calculation and the panel regression later.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 ID_S float qdate long(LT AT) float Book_Lev long(EBITDA CASH) double MV long(PPE SALES SGADMIN DEFTAX PREFSTOCK CAPEX)
"U953101" 184 . . . . . . . . . . .  .
"U265437" 184 . . . . . . . . . . .  .
"F32741W" 184 . . . . . . . . . . .  .
"U679447" 184 . . . . . . . . . . .  .
"F50376N" 184 . . . . . . . . . . .  .
"D307636" 184 . . . . . . . . . . .  .
"D265104" 184 . . . . . . . . . . . 22
"U32273L" 184 . . . . . . . . . . .  .
"U87233C" 184 . . . . . . . . . . .  .
"U671549" 184 . . . . . . . . . . .  .
end
format %tq qdate

[Clyde Schechter]

This is a rather strange data layout. Can you confirm if the following is a correct understanding:

You have a large number of observations for each firm and quarter combined. Each such observation contains a value for one, and only one, of the variables LT AT Book_Lev EBITDA CASH MV PPE SALES SGADMIN DEFTAX PREFSTOCK CAPEX. And the variable Description is not reliable (and so may as well be discarded).

This layout is really not useful for anything I can think of, and it is particularly cumbersome for calculating ratios of these variables. So what you need to do is reduce this to a single observation per firm-quarter that contains the unique value for all of the variables.

collapse (firstnm) LT AT Book_Lev EBITDA CASH///
     MV PPE SALES SGADMIN DEFTAX ///
     PREFSTOCK CAPEX, by(ID_S qdate)

Now you will be well positioned to calculate whatever ratios you like with simple commands like

gen cash_to_mv = cash/MV

[Michael Craig]

There are many observations for the variables (but a lot of missing values). For example, LT (means liabilities) has 46.525 observations not counting the missing values.

The data is from about 2.000 firms on a quarterly basis (2006 to 2015, or 40 quarters).

I thought this would be a nice long layout with all the variables in the rows.

However, Stata does not calc the ratio taking the exact value from say 1. quarter 2006 for LT and AT in order for the ratio to be calculated properly.

[Michael Craig]

This is a part of the data in its origin in Excel:

Description	Code_Mix	ID_S	Data types	Q1 2006	Q2 2006	Q3 2006	Q4 2006	Q1 2007	Q2 2007
VOLKSWAGEN - MARKET VALUE	905009(MV)	D905009	MV	14448,27	17922,25	15470,41	18774,09	24086,88	32124,54
VOLKSWAGEN AG - CAPITAL EXPENDITURES	905009(WC04601A)	D905009	WC04601A	1545000				1631000
VOLKSWAGEN AG - CASH & SHORT TERM INVESTMENTS	905009(WC02001)	D905009	WC02001	15736000	15736000	15736000	15736000	16727000	16727000
VOLKSWAGEN AG - CASH & SHORT TERM INVESTMENTS	905009(WC02001A)	D905009	WC02001A	13126000	14863000	14974000	15736000	15205000	15074000
VOLKSWAGEN AG - COM/PFD PURCHASED, RETIRED, CO	905009(WC04751A)	D905009	WC04751A
VOLKSWAGEN AG - CURRENT PORTION LONG TERM DEBT	905009(WC18232A)	D905009	WC18232A
VOLKSWAGEN AG - DEFERRED TAXES	905009(WC03263A)	D905009	WC03263A				-884000
VOLKSWAGEN AG - EARNINGS BEF INTEREST & TAXES	905009(WC18191A)	D905009	WC18191A	637000	397000	323000	1308000	1085000
VOLKSWAGEN AG - EBIT & DEPRECIATION	905009(WC18198A)	D905009	WC18198A,	2825000	2566000	2441000	3224000	3263000
VOLKSWAGEN AG - ENTERPRISE VALUE	905009(WC18100A)	D905009	WC18100A	64354973	58904225	60551183	65255948	73493059	72842731
VOLKSWAGEN AG - INACTIVE DATE	905009(WC07015)	D905009	WC07015
VOLKSWAGEN AG - INTEREST EXPENSE ON DEBT	905009(WC01251A)	D905009	WC01251A	225000	58000	137000	452000	16000
VOLKSWAGEN AG - LONG TERM DEBT	905009(WC03251A)	D905009	WC03251A	26972000	26785000	27789000	28734000	28626000	30045000
VOLKSWAGEN AG - NET DEBT	905009(WC18199A)	D905009	WC18199A	46843000	43624000	42515000	43021000	43744000	42410000
VOLKSWAGEN AG - NET PROCEEDS FROM SALE/ISSUE C	905009(WC04251A)	D905009	WC04251A
VOLKSWAGEN AG - NET SALES OR REVENUES	905009(WC01001A)	D905009	WC01001A	25337000	26555000	25138000	27845000	26640000	28212000
VOLKSWAGEN AG - PREFERRED STOCK	905009(WC03451A)	D905009	WC03451A	0	0	0	0	0	0
VOLKSWAGEN AG - PROPERTY, PLANT & EQUIP - NET	905009(WC02501)	D905009	WC02501	28379000	28379000	28379000	28379000	27517000	27517000
VOLKSWAGEN AG - PROPERTY, PLANT & EQUIP - NET	905009(WC02501A)	D905009	WC02501A	29851000	29006000	28607000	28379000	27920000	27706000
VOLKSWAGEN AG - RESEARCH & DEVELOPMENT	905009(WC01201A)	D905009	WC01201A					1246000	1544000
VOLKSWAGEN AG - RETURN ON EQUITY - TOTAL (%)	905009(WC08301A)	D905009	WC08301A	5,72	7,76	6,64	10,89	12,19	13,07
VOLKSWAGEN AG - SELLING, GENERAL & ADMINISTRAT	905009(WC01101A)	D905009	WC01101A					4025000	4683000
VOLKSWAGEN AG - SHORT TERM DEBT & CURRENT PORT	905009(WC03051A)	D905009	WC03051A	32997000	31702000	29700000	30023000	30323000	27439000
VOLKSWAGEN AG - TOTAL ASSETS	905009(WC02999A)	D905009	WC02999A	135834000	134396000	134915000	133565000	141892000	143284000
VOLKSWAGEN AG - TOTAL ASSETS (U.S.$)	905009(WC07230A)	D905009	WC07230A	164647108	171861573	171212532	176206962	189695415	193889043
VOLKSWAGEN AG - TOTAL DEBT	905009(WC03255A)	D905009	WC03255A	59969000	58487000	57489000	58757000	58949000	57484000
VOLKSWAGEN AG - TOTAL INTANGIBLE OT ASSETS-NET	905009(WC02649A)	D905009	WC02649A	7599000	7592000	7503000	7193000	7129000	6980000
VOLKSWAGEN AG - TOTAL LIABILITIES	905009(WC03351A)	D905009	WC03351A	111548000	109276000	109690000	106606000	114190000	114407000
VOLKSWAGEN AG - TOTAL SHAREHOLDERS EQUITY	905009(WC03995A)	D905009	WC03995A	24231000	25065000	25170000	26904000	27644000	28814000

[Clyde Schechter]

There are many observations for the variables (but a lot of missing values). For example, LT (means liabilities) has 46.525 observations not counting the missing values.

Yes, but you also have many firms and many quarters. What I'm supposing about your data is that for a given firm and quarter, there is only one distinct non-missing value of each variable. You can verify this by running:

foreach v of varlist LT AT Book_Lev EBITDA CASH///
     MV PPE SALES SGADMIN DEFTAX ///
     PREFSTOCK CAPEX {
          by ID_S qdate (`v'), sort: assert `v' == `v'[1] | missing(`v')
}

If that code does not generate an error, then this property is satisfied and you can use the code I suggested in #5.

If that code does throw an error message, then you have a bigger problem than figuring out the code: you have inconsistent data. Suppose, for example, that for some firm you have two different values of, say, LT in the same quarter. Then how do you know which one to use when you calculate your ratios? You need to resolve that issue before you can code anything.

[Michael Craig]

It seems to have worked. Guess, I need to read something on all these commands in order to speed up my learning process.

Data looks fine.

I am not sure about the panel regression however. I know that I need to define the panel variable and the time variable before by xtset.

I did

. xtset qdate
panel variable: qdate (balanced)
but
got an error message on
xtset ID_S: (string variables not allowed in varlist;
ID_S is a string variable)

I tried (just to see if possible):

xtreg Book_Lev EBITDA CASH which give a result. (Stata makes the group using qdate) - Shouldn't this be the ID_S

I think I need to somehow group the ID_S - then xtset the ID_S and then run the panel regression?

Nevertheless, again an evening with a great learning opportunity for me. Thanks!

[Michael Craig]

That is probably the solution, right:

egen ID = group(ID_S)

. xtset ID qdate
panel variable: ID (strongly balanced)
time variable: qdate, 2006q1 to 2015q4
delta: 1 quarter

[Clyde Schechter]

Yes, that's the solution.