R-Code to VBA Conversion

[willisd413]

Hello All,

This is my first post so sorry if I am starting a thread on the wrong board or if I'm doing this the wrong way! Me and my classmates are required to convert R-Code to VBA for one of our assignments in our masters program. While we have some experience with VBA, we are by no means skilled with it (and we have no experience with R-Code). Our assignment requires us to create a Vasicek Model using VBA. Our professor provided us with the R-Code that creates the model. I pasted the R-Code below, and can provide the link to the site it came from if anyone replies. Any help would be greatly appreciated!
Thanks!
: pray2:
## Simulate Sample Paths ##

## define model parameters
r0 <- 0.03
theta <- 0.10
k <- 0.3
beta <- 0.03

## simulate short rate paths
n <- 10 # MC simulation trials
T <- 10 # total time
m <- 200 # subintervals
dt <- T/m # difference in time each subinterval

r <- matrix(0,m+1,n) # matrix to hold short rate paths
r[1,] <- r0

for(j in 1:n){
for(i in 2:(m+1)){
dr <- k*(theta-r[i-1,j])*dt + beta*sqrt(dt)*rnorm(1,0,1)
r[i,j] <- r[i-1,j] + dr
}
}

## plot paths
t <- seq(0, T, dt)
rT.expected <- theta + (r0-theta)*exp(-k*t)
rT.stdev <- sqrt( beta^2/(2*k)*(1-exp(-2*k*t)))
matplot(t, r[,1:10], type="l", lty=1, main="Short Rate Paths", ylab="rt")
abline(h=theta, col="red", lty=2)
lines(t, rT.expected, lty=2)
lines(t, rT.expected + 2*rT.stdev, lty=2)
lines(t, rT.expected - 2*rT.stdev, lty=2)
points(0,r0)

[SamT]

https://en.wikipedia.org/wiki/Vasicek_model
http://www.investopedia.com/terms/v/vasicek-model.asp
http://bis2information.org/content/Vasicek_model

https://cran.r-project.org/manuals.html
https://en.wikipedia.org/wiki/R_%28programming_language%29
http://www.rexamples.com/
http://tryr.codeschool.com/

Study those, then tell us in common English exactly what you need VBA to do. Sorry, I speak neither R nor Finance.

Speaking Finance is a prerequisite of R, and translating R to English is what I need. I can do English to VBA quite well, thank you.

Also, you need to be aware that VBA Express only help guide you to solutions, we don't actually do homework for students. Specific questions will usually be answered :D

[Kenneth Hobs]

I last used R around 2012 but it seems longer. Like SAS, I found it to have a high learning curve.

Sounds like a tough assignment. It is like translating Spanish to English when you have only grade school Spanish in your toolbox. I have found that once you know one computer programming language, the next is a bit easier. In that light, the instructor's assignment is real-world, somewhat. R is like SAS to a degree but gets its roots from S. It is a scripting language that you pretty well need to just know. R deals with in memory manipulation of matrix variables for the most part. It is closer to a C language than VB, VBA, VBScrit, VBnet. R is similar to S.

You could post a screen snip of the results at each point as most here would not have R. First, you must know how to get those. Learn how to get output. Asking what some step does in R in an R forum, would be my first suggestion. I have not found too many friendly R forums though. You may get comments like RTFM so take a look at the manual as Sam pointed out.

As Sam noted, break your problem into many steps and post separate questions. Though it is still homework and not really allowed by most forums, when you do it like that, I think that it is in the spirit of help that forums offer and not cheating per se, all that much...

Of course there are Excel addins to work with R.

R Tips:
Tinn-R is a good GUI to use with R: http://www.sciviews.org/Tinn-R/
R-reference card: https://cran.r-project.org/doc/contrib/Short-refcard.pdf
R for beginners: https://cran.r-project.org/doc/contrib/Paradis-rdebuts_en.pdf

I even took a graphical R course online several years ago with the author of one of the best graphical R books.

[Paul_Hossler]

## Simulate Sample Paths ##

## define model parameters
r0 <- 0.03
theta <- 0.10
k <- 0.3
beta <- 0.03

## simulate short rate paths
n <- 10 # MC simulation trials
T <- 10 # total time
m <- 200 # subintervals
dt <- T/m # difference in time each subinterval

r <- matrix(0,m+1,n) # matrix to hold short rate paths
r[1,] <- r0

for(j in 1:n){
for(i in 2:(m+1)){
dr <- k*(theta-r[i-1,j])*dt + beta*sqrt(dt)*rnorm(1,0,1)
r[i,j] <- r[i-1,j] + dr
}
}

## plot paths
t <- seq(0, T, dt)
rT.expected <- theta + (r0-theta)*exp(-k*t)
rT.stdev <- sqrt( beta^2/(2*k)*(1-exp(-2*k*t)))
matplot(t, r[,1:10], type="l", lty=1, main="Short Rate Paths", ylab="rt")
abline(h=theta, col="red", lty=2)
lines(t, rT.expected, lty=2)
lines(t, rT.expected + 2*rT.stdev, lty=2)
lines(t, rT.expected - 2*rT.stdev, lty=2)
points(0,r0)



Can you turn your model in pseudo-code using regular words?




Part 1. -define model parameters

initialize correlation r0 to 0.03
initialize some_angle theta to 0.10
initialize another_one k to 0.3
initialize risk beta to 0.03

etc.


Part 6 -- loop to find local max


loop changing
......
until k > 1000





I Googled Vasicek Model ...

[QUOTE][In finance (https://en.wikipedia.org/wiki/Mathematical_finance), the Vasicek model is a mathematical model (https://en.wikipedia.org/wiki/Mathematical_model) describing the evolution of interest rates (https://en.wikipedia.org/wiki/Interest_rate). It is a type of one-factor short rate model (https://en.wikipedia.org/wiki/Short_rate_model) as it describes interest rate movements as driven by only one source of market risk (https://en.wikipedia.org/wiki/Market_risk). The model can be used in the valuation of interest rate derivatives (https://en.wikipedia.org/wiki/Interest_rate_derivative), and has also been adapted for credit markets, although its use in the credit market is in principle wrong, implying negative probabilities (see for example Brigo and Mercurio (2006), Section 21.1.1). It was introduced in 1977 by Oldřich Vašíček (https://en.wikipedia.org/wiki/Old%C5%99ich_Va%C5%A1%C3%AD%C4%8Dek)[1] (https://en.wikipedia.org/wiki/Vasicek_model#cite_note-1) and can be also seen as a stochastic investment model (https://en.wikipedia.org/wiki/Stochastic_investment_model)./QUOTE]

but as SamT says, "I don't speak Finance"


But just some very simple VBA concepts in context of the R model

a. Variables are Dim ... and type defined, Const (ants) are fixed and cannot be changed
b. Assignment is made with = sign
c. Arrays use (x,y,z,...) nomenclature
d. For/Next loops
e. Comments use ' (single quote)

Didn't understand a lot (most) of the model or some statements (marked ???) but maybe this'll get you started.

You can always come back with specific questions





Option Explicit
Sub VBA_Version()

'define model parameters
Const r0 As Double = 0.03
Const theta As Double = 0.1
Const k As Double = 0.3
Const beta As Double = 0.03


Dim n As Long ' MC simulation trials
Dim T As Long ' total time
Dim m As Long ' subintervals
Dim dt As Double ' difference in time each subinterval

'simulate short rate paths
n = 10
T = 10
m = 200
dt = T / m
Dim r(0 To m + 1, 0 To n) As Double ' matrix to hold short rate paths
Dim rnorm(1 To 111, 1 To 111) As Double '???????
'???? r [1,] < -r0
Dim j As Long, i As Long
Dim dr As Double

For j = 1 To n
For i = 2 To m + 1
dr = k * (theta - r(i - 1, j)) * dt + beta * Sqr(dt) * rnorm(1, 0)
r(i, j) = r(i - 1, j) + dr
Next I
Next j

' ## plot paths
' t <- seq(0, T, dt)
' rT.expected <- theta + (r0-theta)*exp(-k*t)
' rT.stdev <- sqrt( beta^2/(2*k)*(1-exp(-2*k*t)))
' matplot(t, r[,1:10], type="l", lty=1, main="Short Rate Paths", ylab="rt")
' abline(h=theta, col="red", lty=2)
' lines(t, rT.expected, lty=2)
' lines(t, rT.expected + 2*rT.stdev, lty=2)
' lines(t, rT.expected - 2*rT.stdev, lty=2)
' points(0,r0)
End Sub