Katherine,
You don't need to convert rate_df into tabular form. You just need to
categorize each row in currency_df into a "tenor". Then you can merge the
two data frames (by currency and tenor). For example ...
# convert dates to R dates, to calculate the number of days to maturity
# I am assuming this is the number of days from the current date to the
maturity date
currency_df$maturity <- as.Date(currency_df$maturity_date, "%d/%m/%Y")
currency_df$current <- as.Date(currency_df$current_date, "%d/%m/%Y")
currency_df$days2mature <- as.numeric(currency_df$maturity -
currency_df$current)
# categorize the number of days to maturity as you wish
# you may need to change the breaks= option to suit your needs
# read about the cut function to make sure you get the cut points included
in the proper category, ?cut
currency_df$tenor <- cut(currency_df$days2mature, breaks=c(0, 1, 7, 14,
seq(from=30.5, length=12, by=30.5)),
labels=c("1 day", "1 week", "2 weeks", "1 month", paste(2:12, "months")))
# merge the currency_df and rate_df
# this will work better with real data, since the example data you provided
didn't have matching tenors
both <- merge(currency_df, rate_df, all.x=TRUE)
Jean
On Wed, Apr 3, 2013 at 5:21 AM, Katherine Gobin
<katherine_go...@yahoo.com>wrote:
> Dear R forum,
>
> (Pl note this is not a finance problem)
>
> I have two data.frames as
>
> currency_df = data.frame(current_date = c("3/4/2013", "3/4/2013",
> "3/4/2013", "3/4/2013"), issue_date = c("27/11/2012", "9/12/2012",
> "14/01/2013", "28/02/2013"), maturity_date = c("27/04/2013", "3/5/2013",
> "14/6/2013", "28/06/2013"), currency = c("USD", "USD", "GBP", "SEK"),
> other_currency = c("EURO", "CAD", "CHF", "USD"), transaction = c("Buy",
> "Buy", "Sell", "Buy"), units_currency = c(100000, 25000, 150000, 40000),
> units_other_currency = c(78000, 25350, 99200, 6150))
>
> rate_df =
> data.frame(date =
> c("28/3/2013","27/3/2013","26/3/2013","25/3/2013","28/3/2013","27/3/2013","26/3/2013",
> "25/3/2013","28/3/2013","27/3/2013","26/3/2013","25/3/2013","28/3/2013","27/3/2013","26/3/2013",
>
> "25/3/2013","28/3/2013","27/3/2013","26/3/2013","25/3/2013","28/3/2013","27/3/2013","26/3/2013",
> "25/3/2013","28/3/2013","27/3/2013","26/3/2013","25/3/2013","28/3/2013","27/3/2013","26/3/2013",
> "25/3/2013","28/3/2013","27/3/2013","26/3/2013","25/3/2013"),
>
> currency = c("USD","USD","USD","USD", "USD", "USD",
> "USD","USD","USD","USD", "USD","USD",
> "GBP","GBP","GBP","GBP","GBP","GBP","GBP","GBP", "GBP","GBP", "GBP","GBP",
> "EURO","EURO","EURO","EURO","EURO","EURO","EURO", "EURO", "EURO","EURO",
> "EURO","EURO"),
>
> tenor = c("1 day","1 day","1 day","1 day","1 week","1 week","1 week","1
> week","2 weeks","2 weeks","2 weeks","2 weeks","1 day","1 day","1 day","1
> day","1 week","1 week","1 week","1 week","2 weeks","2 weeks","2 weeks","2
> weeks","1 day","1 day","1 day","1 day","1 week","1 week","1 week","1
> week","2 weeks","2 weeks","2 weeks","2 weeks"),
>
> rate =
> c(0.156,0.157,0.157,0.155,0.1752,0.1752,0.1752,0.1752,0.1752,0.1752,0.1752,
> 0.1752,0.48625,
> 0.485,0.48625,0.4825,0.49,0.49125,0.4925,0.49,0.49375,0.49125,0.4925,
> 0.49125,0.02643,0.02214,
> 0.02214,0.01929,0.034,0.034,0.034125,0.034,0.044,0.044, 0.041,0.045))
>
> # _______________________________________________________
>
> # 1st data.frame
>
> > currency_df
> current_date issue_date maturity_date currency
> 1 3/4/2013 27/11/2012 27/04/2013 USD
> 2 3/4/2013 9/12/2012 3/5/2013 USD
> 3 3/4/2013 14/01/2013 14/6/2013 GBP
> 4 3/4/2013 28/02/2013 28/06/2013 SEK
> other_currency transaction units_currency
> 1
> EURO Buy 100000
> 2 CAD Buy 25000
> 3 CHF Sell 150000
> 4 USD Buy 40000
> units_other_currency
> 1 78000
> 2
> 25350
> 3 99200
> 4 6150
>
> #
> .......................................................................................
>
> # 2nd data.frame
>
> > rate_df
> date currency tenor rate
> 1 28/3/2013 USD 1 day 0.156000
> 2 27/3/2013 USD 1 day 0.157000
> 3 26/3/2013 USD 1 day 0.157000
> 4 25/3/2013 USD 1 day 0.155000
> 5 28/3/2013 USD 1 week 0.175200
> 6 27/3/2013 USD 1 week
> 0.175200
> 7 26/3/2013 USD 1 week 0.175200
> 8 25/3/2013 USD 1 week 0.175200
> 9 28/3/2013 USD 2 weeks 0.175200
> 10 27/3/2013 USD 2 weeks 0.175200
> 11 26/3/2013 USD 2 weeks 0.175200
> 12 25/3/2013 USD 2 weeks 0.175200
> 13 28/3/2013 GBP 1 day 0.486250
> 14 27/3/2013 GBP 1 day 0.485000
> 15 26/3/2013 GBP 1 day 0.486250
> 16 25/3/2013 GBP 1 day 0.482500
> 17 28/3/2013 GBP 1 week 0.490000
> 18 27/3/2013 GBP 1 week 0.491250
> 19 26/3/2013 GBP 1 week 0.492500
> 20
> 25/3/2013 GBP 1 week 0.490000
> 21 28/3/2013 GBP 2 weeks 0.493750
> 22 27/3/2013 GBP 2 weeks 0.491250
> 23 26/3/2013 GBP 2 weeks 0.492500
> 24 25/3/2013 GBP 2 weeks 0.491250
> 25 28/3/2013 EURO 1 day 0.026430
> 26 27/3/2013 EURO 1 day 0.022140
> 27 26/3/2013 EURO 1 day 0.022140
> 28 25/3/2013 EURO 1 day 0.019290
> 29 28/3/2013 EURO 1 week 0.034000
> 30 27/3/2013 EURO 1 week 0.034000
> 31 26/3/2013 EURO 1 week 0.034125
> 32 25/3/2013 EURO 1 week 0.034000
> 33 28/3/2013 EURO 2 weeks 0.044000
> 34
> 27/3/2013 EURO 2 weeks 0.044000
> 35 26/3/2013 EURO 2 weeks 0.041000
> 36 25/3/2013 EURO 2 weeks 0.045000
>
> # ___________________________________________________
>
> Using plyr and reshape libraries, I have converted the rate_df into
> tabular form as
>
> date USD_1 day USD_1 week USD_2 weeks GBP_1 day
> 1 25/3/2013 0.155 0.1752 0.1752 0.48250
> 2 26/3/2013 0.157 0.1752 0.1752 0.48625
> 3 27/3/2013 0.157 0.1752 0.1752 0.48500
> 4 28/3/2013 0.156 0.1752 0.1752 0.48625
>
> GBP_1 week GBP_2 weeks EURO_1 day EURO_1 week
> 1 0.49000 0.49125 0.01929 0.034000
> 2 0.49250 0.49250 0.02214 0.034125
> 3 0.49125 0.49125 0.02214 0.034000
> 4 0.49000 0.49375 0.02643 0.034000
> EURO_2 weeks
> 1 0.045
> 2 0.041
> 3 0.044
> 4 0.044
>
> # __________________________________________________________
>
> Depending on the maturity period, I have defined discount rates as
>
> # FOR USD
>
>
> if
> (as.character(currency) ==
> "USD")
> {
> if
> (as.character(other_currency) == "GBP" & days_to_maturity <= 1)
>
> {
> libor_rate1 = df_LIBOR_rates$USD_o_n
> libor_rate2 = df_LIBOR_rates$GBP_o_n
> }
>
> else if (as.character(other_currency) == "EURO" & days_to_maturity <= 1)
>
> {
> libor_rate1 = df_LIBOR_rates$USD_o_n
> libor_rate2 = df_LIBOR_rates$EUR_o_n
> }
>
> ......................
> ......................
>
>
> if
> (as.character(other_currency) == "GBP" & (days_to_maturity > 1 &
> days_to_maturity <= 7))
>
> {
> libor_rate1 = df_LIBOR_rates$USD_1w
> libor_rate2 = df_LIBOR_rates$GBP_1w
> }
>
> else if (as.character(other_currency) == "EURO" & (days_to_maturity > 1
> & days_to_maturity <= 7))
>
> {
> libor_rate1 = df_LIBOR_rates$USD_1w
>
> libor_rate2 = df_LIBOR_rates$EUR_1w
> }
>
> ............................
> ............................
>
>
>
> Similarly for other currencies too ...
>
> # __________________________________________________
>
> # My PROBLEM
>
> In reality, I am dealing with at least (for the time being and will only
> increase in future) 10 currencies (LIBORs) only and each currency has about
> 15 tenors. So effectively, I have ended up writing 45*15*15 = 10125 such
> "if statements" only for assigning the rates depending on the tenor.
> (Tenors are overnight, 1 week, 2 weeks, 1 month, 2 months, 3 months, 4
> months, 5 months, 6 months, 7 months, 8 months, 9 months, 10 months, 11
> months, 12 months).
>
> The code is working and I am able to achieve what I need to. But, I think
> this is a foolish method of assigning the rates and each time a new
> currency is added, I will have to rewrite the whole code again. Also, the
> processing time is tremendous as it's taking me at least 10+ minutes.
>
> I am not a professional and hence this is my earnest request -
>
> Is it possible or is there any other way to assign the rates depending on
> the currency, other currency and tenor and also
> depending on the maturity period where maturity period is the difference
> in days between the maturity_date and current_date?
>
> Kindly guide
>
> Katherine
>
>
>
>
>
> [[alternative HTML version deleted]]
>
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
