SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
Panoramix
Topic Author
Posts: 279
Joined: March 20th, 2008, 4:13 pm

Need help with my VBA program

July 1st, 2008, 10:11 am

Hi all!In these days i am on holiday, but i cannot give up working on my poor VBA skills.I tried to implement an easy FRN pricing program, but i had a problem:if i run the program using these inputsIssue date: 20/7/2005Expiration date:20/7/2010Notional:100Previous Libor:0.040 Spread:-0.001Previous coupon:20/1/2008Coupon expipiration:20/7/2008N° of payment:2Basis:1I have two errrors in the price computation:1)in the time it writes 39649 in b5 instead of 202) it skips the next coupon date while if i use other dates it seems to work properly. I want to state that it is nota a homework or something like that but just for my own interest, so if you can take a look at this stuff and tell me where I am making a mistake I will be very gratefull.ThanksPs: i cannot attach the xls file so i posted all my code.If you wanna run the code you have towrite the interest rates in the range("c2:c26") (use the Bloomberg ones)write Range("a27") Security name(xyz)write Range("b28")Issue date(27/5/2005)write Range("b29")Expiration datewrite Range("b30")Notionalwrite Range("b31")Previous Libor write Range("b32")Spreadwrite Range("b33")Previous couponwrite Range("b34")Coupon expipirationwrite Range("b35")N° of paymentwrite Range("b36")Basis
 
User avatar
CrashedMint
Posts: 2591
Joined: January 25th, 2008, 9:12 pm

Need help with my VBA program

July 1st, 2008, 11:09 am

I know, that's not the answers you were looking for, but WHY are you doing this stuff in Excel? Wouldn't C++ or Matlab much faster/better?
 
User avatar
Panoramix
Topic Author
Posts: 279
Joined: March 20th, 2008, 4:13 pm

Need help with my VBA program

July 1st, 2008, 12:40 pm

Totally true, but i am not a great programmer as you can easily see.I am studing a little bit of c++ in my free time, but VBA is easier (or it should be).I am gonna try to rebuild it in c++ later, but i would have a working programm just in order to compare the outputs.
 
User avatar
vbc
Posts: 14
Joined: June 6th, 2006, 10:40 pm

Need help with my VBA program

July 3rd, 2008, 12:47 am

Why should it be 20 in cell b5? You should also have declared at the beginning of your code Option Explict
 
User avatar
Panoramix
Topic Author
Posts: 279
Joined: March 20th, 2008, 4:13 pm

Need help with my VBA program

July 3rd, 2008, 5:55 am

QuoteOriginally posted by: vbcWhy should it be 20 in cell b5? You are right. 20 is not in cell b5 in fact if you take a look at the code in cell b5 there is 4/12=0.333. my problem is that if i use these inputs Issue date: 20/7/2005Expiration date:20/7/2010Notional:100Previous Libor:0.040 Spread:-0.001Previous coupon:20/1/2008Coupon expipiration:20/7/2008N° of payment:2Basis:1i basically have 2 errors:1)in cell b17 it appears 39649 instead of 202)the proggy is not taking into account the first coupon, but starts computing from the second one.If you are interested in helping me some more i can send you a jpeg image with errors, cause i cannot upload it in the forum.QuoteOriginally posted by: vbc You should also have declared at the beginning of your code Option ExplictI forgot it.Ty very much for your tip.
 
User avatar
CrashedMint
Posts: 2591
Joined: January 25th, 2008, 9:12 pm

Need help with my VBA program

July 3rd, 2008, 8:40 am

QuoteOriginally posted by: PanoramixTotally true, but i am not a great programmer as you can easily see.Neither am I -- but to be honest, I find Excel harder than Matlab or C++, mostly because iot produces really really bulky code. I mean, common! When I look at stugff like this: "Range("d26").Formula = (1 - Range("c26") * (Range("d14") + Range("d15") + Range("d16") + Range("d17") + Range("d18") + Range("d19") + Range("d20") + Range("d21") + Range("d22") + Range("d23") + Range("d24") + Range("d25"))) / (1 + Range("c26"))" my head hurts. ;-)Crash
 
User avatar
Panoramix
Topic Author
Posts: 279
Joined: March 20th, 2008, 4:13 pm

Need help with my VBA program

July 3rd, 2008, 8:47 am

QuoteOriginally posted by: CrashedMintRange("d26").Formula = (1 - Range("c26") * (Range("d14") + Range("d15") + Range("d16") + Range("d17") + Range("d18") + Range("d19") + Range("d20") + Range("d21") + Range("d22") + Range("d23") + Range("d24") + Range("d25"))) / (1 + Range("c26"))" my head hurts. ;-)CrashMy head hurts 2.Where did you start studying c++?Maybe, as soon as i got a little bit of time (you know...sunbathing is hard, so many horny girls...) i am gonna try implementing it and i will again ask for some help.TY
 
User avatar
endian675
Posts: 143
Joined: March 11th, 2008, 7:09 pm

Need help with my VBA program

July 11th, 2008, 10:53 am

Use C#. 90% of the benefit of learning C++ (similar syntax, structure, flow) with none of the crappy build problems and a nice integrated dev environment that's free (Visual Studio Express 2005/8). Then once you've solved the problem, you can re-code in C++.
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On