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]]
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_gobin@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]]
Katherine, You should cc the R-help on all correspondence. The more eyes that see your query, the quicker and probably the better the response will be. Send your message as plain text with no attachments ... so, include your code, and use dput() to share some example data. Jean On Thu, Apr 4, 2013 at 3:19 AM, Katherine Gobin <katherine_gobin@yahoo.com>wrote:> Dear Mr Adams, > > I sincerely apologize for taking the liberty of writing to you. I > wholeheartedly thank you for the wonderful solution you had provided me > yesterday. I have customized the R code you had provided and it's yielding > the results. I can't imagine me repeating the 10000 lines code after > receving such a powerful solution from you. In future it will save lots of > efforts from my side as I always deal with such situation. > > There is one small problem though - > > I am dealing with pair of currencies > > e.g. currency other_currency transaction > USD EURO Buy > USD CAD Buy > GBP CHF Sell > SEK USD Buy > > > The R code gives me the currency rates (w.r.t. appropriate "tenor"), > however, I need the corresponding rates pertaining to the other currency > too i.e. in the first case, the maturity period applicable is one month so > the R - code gives me one month LIBOR wr.t. USD, but I need the > corresponding one month LIBOR w.r.t. the other currency i.e. EURO in this > case. > > I tried to improve upon the merge statement and used "?merge", but > couldn't. Another problem is the order of the original portfolio is not > mainteained , but I think I can manage the order. > > With warm regards > > > Katherine > > > > > > > > > --- On *Wed, 3/4/13, Adams, Jean <jvadams@usgs.gov>* wrote: > > > From: Adams, Jean <jvadams@usgs.gov> > Subject: Re: [R] Better way of writing R code > To: "Katherine Gobin" <katherine_gobin@yahoo.com> > Cc: "R help" <r-help@r-project.org> > Date: Wednesday, 3 April, 2013, 2:08 PM > > 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_gobin@yahoo.com<http://mc/compose?to=katherine_gobin@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 <http://mc/compose?to=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]]
On Thu, Apr 4, 2013 at 9:32 AM, Adams, Jean <jvadams at usgs.gov> wrote:> Katherine, > > You should cc the R-help on all correspondence. > The more eyes that see your query, the quicker and probably the better the > response will be. > Send your message as plain text with no attachments ... so, include your > code, and use dput() to share some example data. >Although many types of attachments are not allowed it seems that .txt, .R, .png, .pdf and possibly certain other types are accepted.
Katherine,
To preserve the original order, you could create a new variable for the
currency data frame (BEFORE the merges), then use this variable to reorder
at the end.
currency_df$orig.order <- 1:dim(currency_df)[1]
You can do another merge for the other currency, you just need to specify
the columns that you want to merge by. The rate information will be called
rate.x for the first currency (from the first merge) and rate.y for the
other currency (from the second merge).
both2 <- merge(both, rate_df, by.x=c("other_currency",
"tenor"),
by.y=c("currency", "tenor"), all.x=TRUE)
Then reorder.
both2 <- both2[order(both2$orig.order), ]
Jean
On Thu, Apr 4, 2013 at 3:19 AM, Katherine Gobin
<katherine_gobin@yahoo.com>wrote:
> Dear Mr Adams,
>
> I sincerely apologize for taking the liberty of writing to you. I
> wholeheartedly thank you for the wonderful solution you had provided me
> yesterday. I have customized the R code you had provided and it's
yielding
> the results. I can't imagine me repeating the 10000 lines code after
> receving such a powerful solution from you. In future it will save lots of
> efforts from my side as I always deal with such situation.
>
> There is one small problem though -
>
> I am dealing with pair of currencies
>
> e.g. currency other_currency transaction
> USD EURO Buy
> USD CAD Buy
> GBP CHF Sell
> SEK USD Buy
>
>
> The R code gives me the currency rates (w.r.t. appropriate
"tenor"),
> however, I need the corresponding rates pertaining to the other currency
> too i.e. in the first case, the maturity period applicable is one month so
> the R - code gives me one month LIBOR wr.t. USD, but I need the
> corresponding one month LIBOR w.r.t. the other currency i.e. EURO in this
> case.
>
> I tried to improve upon the merge statement and used "?merge",
but
> couldn't. Another problem is the order of the original portfolio is
not
> mainteained , but I think I can manage the order.
>
> With warm regards
>
>
> Katherine
>
>
>
>
>
>
>
>
> --- On *Wed, 3/4/13, Adams, Jean <jvadams@usgs.gov>* wrote:
>
>
> From: Adams, Jean <jvadams@usgs.gov>
> Subject: Re: [R] Better way of writing R code
> To: "Katherine Gobin" <katherine_gobin@yahoo.com>
> Cc: "R help" <r-help@r-project.org>
> Date: Wednesday, 3 April, 2013, 2:08 PM
>
> 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_gobin@yahoo.com<http://mc/compose?to=katherine_gobin@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 <http://mc/compose?to=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]]