VBA for MS Excel

K

Kakane

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
Hi,
Has anyone learnt VBA from scratch lately? I’ve been trying (and failing) to do so.
I don’t want to appear eager to run before I can walk here…just need a VBA project that’ll hold my interest initially…I didn’t learn guitar by playing ‘three blind mice’, catchy Oasis songs did the trick! I digress…
What am I looking to do? I’m hoping to get good enough with VBA to design a mean-variance optimisation macro and/or functions ie. for my own investments. Once I achieve that, I’ll be hooked on VBA.
I have run a few Google searchs for MVOs, but the VBA code is beyond me at this moment…
Any advice, website suggestions would be greatly appreciated.
Cheers,
PS: Since I’m bored to tears at work, pursuit of VBA competence will be constructive use of my time.
 
Nice…
MVO - may be in your opinion but I want to simply see for myself if I can construct efficient frontiers in Excel.
Anyone have anything helpful to forward?
 
hi Kakane, I know VBA quite well and have used it for years. I would absolutely recommend that you pursue this and it’s much easier and better to learn if you have a specific goal in mind, as you do. i would google for VBA tutorials, even better will be to find them related to finance!
try this first and let me know if you have any questions:
http://www.fontstuff.com/vba/vbatut01.htm
There is lots of stuff here http://www.anthony-vba.kefra.com/index_002.htm
including portfolio optimization: http://www.anthony-vba.kefra.com/vba/vba8.htm
I haven’t tried any of his code so can’t vouch for it, but you will have fun learning!
but anyway it’s best to do it on your own.
 
Regardless of whether MVO is a sensible way to manage portfolios, I’m not sure if MVO optimizer is the best bet for a first VBA project, though.
Getting solver to work in VBA is kinda complicated, as I recall. It’s fine if you already have some VBA knowledge - then it ends up being the main part of the work - but if you are a newbie, you might want a more basic project.
Maybe try to make something that produces a spot rate curve from par yields via the bootstrapping method.
 
Maratikus,
No I haven’t used Solver before. I’ve just installed it and have put together stats for 5 OEICs:
1) Cov matrix
2) 10yr return (annualised)
3) 10yr Stdev
4) Correlations b/w each
I’ve found a word document giving a few tips, so I’ll see how I go from there..
Thanks for the suggestion of solver. Why bother with VBA if solver can do it.
Cheers,
 
Doubledip - I’ll check out the sites.
Solver - I’m kind of confused here. Can I not use solver without VBA code, I assume that Tools/Solver is all I need? Am I wrong?
Thanks
 
Yes you can use solver without VBA code. However, for complex problems requiring iteration, say, you may wish to call Solver from within your code. It’s a bit tricky though, and I don’t usually do it anymore, preferring to write my own code.
 
You can use solver w/o VBA code, and that is worth knowing how to do (and very useful at times).
But I thought your post said that this is a project to learn VBA. If you are just learning VBA, it’s best not to start with questions like “how do I call solver from VBA,” you will very likely get extremely frustrated and possibly give up.
It is also true that if you want a very flexible portfolio optimizer in VBA, you would want to write something that calls solver. On the other hand, if you are actually wanting a very flexible optimizer, you might prefer to purchase one of the other solver-like plug-ins to VBA. I’ve heard lots of complaints about excel’s solver algorithm (often doesn’t find true maxima, etc.).
 
bchadwick Wrote:
> I’ve heard
> lots of complaints about excel’s solver algorithm
> (often doesn’t find true maxima, etc.).
I second that. The algorithm itself (SQP) should be a good one but for some reason its implementation in solver is poor. Matlab’s optimizer is much better.
 
maratikus Wrote:
——————————————————-
> bchadwick Wrote:
> > I’ve heard
> > lots of complaints about excel’s solver
> algorithm
> > (often doesn’t find true maxima, etc.).
>
> I second that. The algorithm itself (SQP) should
> be a good one but for some reason its
> implementation in solver is poor. Matlab’s
> optimizer is much better.
Pity that I can’t get a MatLab license unless my role requires it. The quants in my office prefer it to MS Excel for the reasons you hinted towards. Though I’m determined to broaden my skillset - cheers for encouragement everyone!
Iterations - undoubtedly I’ll need to get a handle on this for when (and if) I venture into Monte Carlo simulation…
Night all
 
Excellent, my weekend got a purpose: Learn VBA, a bit. Thanks for the suggestion. Feels strange that I really never tried it before.
Regarding MatLab: Much better than Excel, i.e. you don’t want Excel for Monte Carlo. Fortunatly, you can do more complex work in MatLab and then get that stuff into Excel for presentation purpose.
There are actually two ways to get a cheap MatLab license for learning/personal use: 1.) Try to get a trial version 2.) Get into a university programm what has free MatLab licenses.
 
Re matlab: a friend just told me about a free product called scilab which he said provides a reasonable alternative to matlab. He also said that there are free matlab libraries within the free statistical software R. I have not tried either of these so can’t vouch for them, but there are many of us in the same boat - company won’t pay for licenses - and this might help. R is good to know in any case.
 
Re R: Yes, I’ve installed R a while back, but since I know MatLab from school, I feel less urge to invest time into R. Many friends more into math like R better than MatLab.
 
You must log in or register to reply here.
Back
Top