AahuM
Reputation: 73
match values with different dates data.table
I have an issue I just dont seem to be able to solve. the issue is the following: my data looks approx like this, it's a data.table:
Code Date Marketvalue CommonEquity FiscalYearEnd
1: 13643N 2001-03-30 44.01 26761 2001-09-30
2: 13643N 2001-04-30 35.21 26761 2001-09-30
3: 13643N 2001-05-31 48.82 26761 2001-09-30
4: 13643N 2001-06-29 31.74 26761 2001-09-30
5: 13643N 2001-07-31 32.01 26761 2001-09-30
6: 13643N 2001-08-31 20.27 26761 2001-09-30
7: 13643N 2001-09-28 15.20 26761 2001-09-30
8: 13643N 2001-10-31 18.14 18618 2002-09-30
9: 13643N 2001-11-30 22.14 18618 2002-09-30
10: 13643N 2001-12-31 18.67 18618 2002-09-30
11: 13643N 2002-01-31 21.34 18618 2002-09-30
12: 13643N 2002-02-28 18.19 18618 2002-09-30
13: 13643N 2002-03-29 19.47 18618 2002-09-30
14: 13643N 2002-04-30 17.34 18618 2002-09-30
15: 13643N 2002-05-31 13.07 18618 2002-09-30
16: 13643N 2002-06-28 7.47 18618 2002-09-30
17: 13643N 2002-07-31 7.47 18618 2002-09-30
18: 13643N 2002-08-30 6.46 18618 2002-09-30
19: 13643N 2002-09-30 5.87 18618 2002-09-30
20: 13643N 2002-10-31 6.67 13728 2003-09-30
21: 13643N 2002-11-29 4.16 13728 2003-09-30
22: 13643N 2002-12-31 3.47 13728 2003-09-30
23: 13643N 2003-01-31 3.36 13728 2003-09-30
24: 13643N 2003-02-28 3.36 13728 2003-09-30
25: 13643N 2003-03-31 2.61 13728 2003-09-30
26: 13643N 2003-04-30 3.04 13728 2003-09-30
27: 13643N 2003-05-30 3.84 13728 2003-09-30
28: 13643N 2003-06-30 4.43 13728 2003-09-30
Code Date Marketvalue CommonEquity FiscalYearEnd
what I want to do: each june of each year t I want to match the common equity of the fiscal year which ended in t-1 with the market value of the previous december. now getting the marketvalue of the previous december is not that hard: I used the shift function with n= 6L to shift the december market values to june, but how do I do it with the common equity? eg: for june 2002, the fiscal year end of that fiscal year is in 2002-09-30, but I need the common equity of the fiscal year which ended in t-1, in this case of 2001-09-30 (the value is 26761, see commonequity column). can anybody help me out on this? I unfortunately dont have any code of my own for this problem as I simply dont know how to adress the fiscal year end issue :(
thanks in advance.
EDIT:
I included an expected output:
Code Date Marketvalue CommonEquity FiscalYearEnd BEMERatio
1: 13643N 2001-03-30 44.01 26761 2001-09-30 NA
2: 13643N 2001-04-30 35.21 26761 2001-09-30 NA
3: 13643N 2001-05-31 48.82 26761 2001-09-30 NA
4: 13643N 2001-06-29 31.74 26761 2001-09-30 NA
5: 13643N 2001-07-31 32.01 26761 2001-09-30 NA
6: 13643N 2001-08-31 20.27 26761 2001-09-30 NA
7: 13643N 2001-09-28 15.20 26761 2001-09-30 NA
8: 13643N 2001-10-31 18.14 18618 2002-09-30 NA
9: 13643N 2001-11-30 22.14 18618 2002-09-30 NA
10: 13643N 2001-12-31 18.67 18618 2002-09-30 NA
11: 13643N 2002-01-31 21.34 18618 2002-09-30 NA
12: 13643N 2002-02-28 18.19 18618 2002-09-30 NA
13: 13643N 2002-03-29 19.47 18618 2002-09-30 NA
14: 13643N 2002-04-30 17.34 18618 2002-09-30 NA
15: 13643N 2002-05-31 13.07 18618 2002-09-30 NA
16: 13643N 2002-06-28 7.47 18618 2002-09-30 1433.369
17: 13643N 2002-07-31 7.47 18618 2002-09-30 NA
18: 13643N 2002-08-30 6.46 18618 2002-09-30 NA
19: 13643N 2002-09-30 5.87 18618 2002-09-30 NA
20: 13643N 2002-10-31 6.67 13728 2003-09-30 NA
21: 13643N 2002-11-29 4.16 13728 2003-09-30 NA
22: 13643N 2002-12-31 3.47 13728 2003-09-30 NA
23: 13643N 2003-01-31 3.36 13728 2003-09-30 NA
24: 13643N 2003-02-28 3.36 13728 2003-09-30 NA
25: 13643N 2003-03-31 2.61 13728 2003-09-30 NA
26: 13643N 2003-04-30 3.04 13728 2003-09-30 NA
27: 13643N 2003-05-30 3.84 13728 2003-09-30 NA
28: 13643N 2003-06-30 4.43 13728 2003-09-30 5365.418
Code Date Marketvalue CommonEquity FiscalYearEnd BEMERatio
we dont have a bemeratio for june 2001 as we dont have a marketvalue from december 2000 in our data table, but for june 2002 and june 2003. basically: each june: divide the common equity of the fiscal year which ended in the previous calendar year by the market value of the previous december. in this example for june 2002: 26761 (common equity of the fiscal year which ended in 2001) divided by 18.67 (the marketvalue of december 2001).
for june 2003: 18618 (common equity of the fiscal year which ended in 2002) divided by 3.47 (the market value of december 2002)
Upvotes: 0
Views: 82
Answers (1)
chinsoon12
Reputation: 25225
Here is a possible approach using data.table:
#calculate the prev fiscal year end date for self joining later
DT[!is.na(FiscalYearEnd),
PrevFiscalYearEnd := as.Date(vapply(FiscalYearEnd,
function(x) seq(x, by="-1 year", length.out=2L)[2L],
numeric(1L)),
origin="1970-01-01")]
ans <- DT[!is.na(FiscalYearEnd), BEMERatio :=
#get the CommonEquity for each fiscal year end
unique(DT[!is.na(FiscalYearEnd), .(Code, FiscalYearEnd, CommonEquity)])[
#join to find the previous fiscal year end common equity
DT[!is.na(FiscalYearEnd)], on=c("Code", "FiscalYearEnd"="PrevFiscalYearEnd")][,
#calculate ratio (need to make sure data is complete so that shifting by 6 months is robust)
CommonEquity / shift(Marketvalue, 6L)]
]
output:
Code Date Marketvalue CommonEquity FiscalYearEnd PrevFiscalYearEnd BEMERatio
1: 13643N 2001-03-30 44.01 26761 2001-09-30 2000-09-30 NA
2: 13643N 2001-04-30 35.21 26761 2001-09-30 2000-09-30 NA
3: 13643N 2001-05-31 48.82 26761 2001-09-30 2000-09-30 NA
4: 13643N 2001-06-29 31.74 26761 2001-09-30 2000-09-30 NA
5: 13643N 2001-07-31 32.01 26761 2001-09-30 2000-09-30 NA
6: 13643N 2001-08-31 20.27 26761 2001-09-30 2000-09-30 NA
7: 13643N 2001-09-28 15.20 26761 2001-09-30 2000-09-30 NA
8: 13643N 2001-10-31 18.14 18618 2002-09-30 2001-09-30 760.0398
9: 13643N 2001-11-30 22.14 18618 2002-09-30 2001-09-30 548.1565
10: 13643N 2001-12-31 18.67 18618 2002-09-30 2001-09-30 843.1317
11: 13643N 2002-01-31 21.34 18618 2002-09-30 2001-09-30 836.0200
12: 13643N 2002-02-28 18.19 18618 2002-09-30 2001-09-30 1320.2269
13: 13643N 2002-03-29 19.47 18618 2002-09-30 2001-09-30 1760.5921
14: 13643N 2002-04-30 17.34 18618 2002-09-30 2001-09-30 1475.2481
15: 13643N 2002-05-31 13.07 18618 2002-09-30 2001-09-30 1208.7173
16: 13643N 2002-06-28 7.47 18618 2002-09-30 2001-09-30 1433.3690
17: 13643N 2002-07-31 7.47 18618 2002-09-30 2001-09-30 1254.0300
18: 13643N 2002-08-30 6.46 18618 2002-09-30 2001-09-30 1471.1930
19: 13643N 2002-09-30 5.87 18618 2002-09-30 2001-09-30 1374.4735
20: 13643N 2002-10-31 6.67 13728 2003-09-30 2002-09-30 1073.7024
21: 13643N 2002-11-29 4.16 13728 2003-09-30 2002-09-30 1424.4836
22: 13643N 2002-12-31 3.47 13728 2003-09-30 2002-09-30 2492.3695
23: 13643N 2003-01-31 3.36 13728 2003-09-30 2002-09-30 2492.3695
24: 13643N 2003-02-28 3.36 13728 2003-09-30 2002-09-30 2882.0433
25: 13643N 2003-03-31 2.61 13728 2003-09-30 2002-09-30 3171.7206
26: 13643N 2003-04-30 3.04 13728 2003-09-30 2002-09-30 2791.3043
27: 13643N 2003-05-30 3.84 13728 2003-09-30 2002-09-30 4475.4808
28: 13643N 2003-06-30 4.43 13728 2003-09-30 2002-09-30 5365.4179
Code Date Marketvalue CommonEquity FiscalYearEnd PrevFiscalYearEnd BEMERatio
if you really need months outside of June to be NA:
ans[month(Date)!=6L, BEMERatio := NA_real_]
output:
Code Date Marketvalue CommonEquity FiscalYearEnd PrevFiscalYearEnd BEMERatio
1: 13643N 2001-03-30 44.01 26761 2001-09-30 2000-09-30 NA
2: 13643N 2001-04-30 35.21 26761 2001-09-30 2000-09-30 NA
3: 13643N 2001-05-31 48.82 26761 2001-09-30 2000-09-30 NA
4: 13643N 2001-06-29 31.74 26761 2001-09-30 2000-09-30 NA
5: 13643N 2001-07-31 32.01 26761 2001-09-30 2000-09-30 NA
6: 13643N 2001-08-31 20.27 26761 2001-09-30 2000-09-30 NA
7: 13643N 2001-09-28 15.20 26761 2001-09-30 2000-09-30 NA
8: 13643N 2001-10-31 18.14 18618 2002-09-30 2001-09-30 NA
9: 13643N 2001-11-30 22.14 18618 2002-09-30 2001-09-30 NA
10: 13643N 2001-12-31 18.67 18618 2002-09-30 2001-09-30 NA
11: 13643N 2002-01-31 21.34 18618 2002-09-30 2001-09-30 NA
12: 13643N 2002-02-28 18.19 18618 2002-09-30 2001-09-30 NA
13: 13643N 2002-03-29 19.47 18618 2002-09-30 2001-09-30 NA
14: 13643N 2002-04-30 17.34 18618 2002-09-30 2001-09-30 NA
15: 13643N 2002-05-31 13.07 18618 2002-09-30 2001-09-30 NA
16: 13643N 2002-06-28 7.47 18618 2002-09-30 2001-09-30 1433.369
17: 13643N 2002-07-31 7.47 18618 2002-09-30 2001-09-30 NA
18: 13643N 2002-08-30 6.46 18618 2002-09-30 2001-09-30 NA
19: 13643N 2002-09-30 5.87 18618 2002-09-30 2001-09-30 NA
20: 13643N 2002-10-31 6.67 13728 2003-09-30 2002-09-30 NA
21: 13643N 2002-11-29 4.16 13728 2003-09-30 2002-09-30 NA
22: 13643N 2002-12-31 3.47 13728 2003-09-30 2002-09-30 NA
23: 13643N 2003-01-31 3.36 13728 2003-09-30 2002-09-30 NA
24: 13643N 2003-02-28 3.36 13728 2003-09-30 2002-09-30 NA
25: 13643N 2003-03-31 2.61 13728 2003-09-30 2002-09-30 NA
26: 13643N 2003-04-30 3.04 13728 2003-09-30 2002-09-30 NA
27: 13643N 2003-05-30 3.84 13728 2003-09-30 2002-09-30 NA
28: 13643N 2003-06-30 4.43 13728 2003-09-30 2002-09-30 5365.418
Code Date Marketvalue CommonEquity FiscalYearEnd PrevFiscalYearEnd BEMERatio
Upvotes: 1
Related Questions
- Matching dates but providing a few days leeway using data.table in R
- Matching nearest date between two data.frames
- Retrieving value based on matched dates
- Matching values by nearest POSIXct in two data frames keeping both dates
- How to match value based on time range from another table?
- Matching 2 date columns with unequal length
- R: Pattern-matching financial time-series data with 2 large data sets:
- R column matching
- if statement for matched values based on dates in R
- Matching multiple date values in R