Need VBA / Excel (Macro programming) resources

I am looking out for good VBA / Excel (Macro programming) books or resources online.

I am not new to programming, have programmed for a while now and in several programming languages (C, C++, Java, etc). Had done VBA in my curriculum 10 years ago, but never took it seriously.

Here I am, trying to correct a miskate from the past - I should have taken up VBA than any of the aforementioned languages. It has so much practical applications and when it’s linked to Excel - it’s GOLD.

Being a charterholder with no excel (macro) skills is like a big fat ZERO.

Any links, websites suggested.

since you already know how to program, you have a leg up. Start with this, http://api.ning.com/files/8fQTDF096XOzHDjo-Ea1729Rwe9JmHgjmEnXoTi*5dQkCyK2Q-NCnfVaMetc*XQ96YIt*sCnRrzemjWfQRrF6D3VNEs5sWKO/15253251FinancialModelingbyChandanSengupta.pdf

ch 15 though there are good tips throughout the document. There’s another thread on this started by passme where I had a number of other references, you can check that if you like as well. For now, here’s a quick example. In Excel, you want to create a profit function. It takes revenue and cost as inputs. Put the value 100 in cell A2 as revenue, and 70 in cell B2 as “cost”. You can label A1 as “Revenue” , B1 as “Cost” and C1 as “Profit” if you like. Now, what is the profit? In C2, you would type =A2 - B2. Simple stuff, but now, let’s write a profit function in Excel. Excel doesn’t already have a built in profit function, but we can create it easily. First make sure you have the Developer tab showing on your toolbar. if you don’t know what i mean see this http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx

then click that tab, click Visual Basic, click Insert, Module. On the area that appears, type the following two lines: you would normally type a third line, “end function” but you will see that VBA inserts this for you.

function profit(revenue, cost)

profit = revenue - cost

then go back to excel, and in another cell, type =profit(A2,B2).

that’s it. You can now also see your new function in the function browser, search for User Defined functions.

Now: write a function to compute profit margin, build up a small set of financial statements so you can compute the ROE using dupont analysis.

I learned VBA from this website:

http://excelexperts.com/Learn-VBA

I need to step up my VBA game. I don’t get why employers stress macros so much, all you have to do is hit the record button, perform the steps you want and assign it to a keyboard function. I haven’t built anything from scratch using VBA, but I’ve used it to edit existing macros.

Is C++ something you can learn on your own? Should I take a course through a community college?

The best site is Pearsons for vba. Get Walkenbach as a good reference book, he does one for vba and one for formulas. My vba tip of the day is for you to use dictionaries via referencing ms scripting.runtime. Very poweful instead of using arrays.

So what do you guys use VBA for? The only time i touched VBA was at university for a mandatory project…it was something about valuing warrants with monte carlo simulation.

Sometimes it’s nice to write your own functions. You can write something that turns a sequence of levels into a sharpe ratio, given an RFR and periodicity parameter, for example.

Thanks DoubleDip & nodes - Heard a lot about the Chandan Sengupta and Simon Benninga Financial Modelling books. Will give them a try.

Thanks cleverCFA & ohai for the references and resources.

bpdulog - C++ is definitely a tough one, if you are not aware of the Object Oriented Paradigm and coming from a straight up procedural language like C, then the learning curve is MASSIVE for C++ or Java/J2EE. Even tougher is language which follow Aspect Oriented Programming (AOP) like Spring Framework.

Heard a lot of HF shops need C++ working knowledge, not sure what’s the deal there?

bchadwick - Would you know what most of the Algo Trading strategies programmed in? Python? Perl? C ?

Look at Youtube videos and go through Financial Modeling by Simon Benninga.

run the following code. Open a new workbook and type it into a VBA module, then from your worksheet, select Macros/run fillproducts

this cracked me up when I realized what it was. meep meep!

Private Function getProducts(ByVal supplier As String) As Collection Dim getProducts_ As New Collection If supplier = “ACME” Then getProducts_.Add (“Anvil”) getProducts_.Add (“Earthquake Pills”) getProducts_.Add (“Dehydrated Boulders”) getProducts_.Add (“Disintegrating Pistol”) End If Set getProducts = getProducts_ Set getProducts_ = Nothing End Function Sub fillProducts() Dim products As Collection Set products = getProducts(“ACME”) For i = 1 To products.Count Sheets(1).Cells(i, 1).Value = products(i) Next i End Sub

^ Tried this. It does nothing more than print in cells A1 to A4 the below list

Anvil Earthquake Pills Dehydrated Boulders Disintegrating Pistol

Am I missing something?

As far as I know, most production work is still done in C, C#, or C++ variants, occasionally Java. There are huge libraries of quant-related functions in C++, and some have been ported to Java. Faster processing makes some stuff more feasible in Java than it used to be, but HFT stuff is necessarily done in something like C++ or even directly done in machine code if microseconds are expected to make a difference. I don’t do HFT stuff myself.

Prototyping and testing is often done in Matlab or R or S-plus if speed is not a major issue, because they are more easily scripted and modified. If it’s simple enough, some strategies can sometimes be done in Excel.

Matlab has some stuff that will actually take matlab code and produce C code that does the same thing. I’ve never used it, but I thought that sounded neat.

no you are not.

Before you start the process, make sure you want to be in front of your computer screen like a slave for 10 hours a day.

yes it’s funny because these are the Acme Products from the old road runner cartoons. I didn’t expect this so had a laugh when I realized what these were.

bchad, I’m doing some work with Matlab -> Excel now, it works pretty well. as you say they can create compilable C, java etc as well.

You can do a LOT with Excel VBA. The last place I worked at, we ran pretty much our entire fund front to back in Excel VBA. We had some 3rd party software for market data and portfolio management (i.e. position keeping, corporate action processing, cash flow management, etc), but everything else was basically done in VBA. Don’t underestimate the power of VBA. It’s super powerful in the hands of a good developer. If you think VBA is limited to Macro-Record type stuff then think again.

Regarding prototyping trading strategies, I’ve seen stuff done in Excel but I see Python as being a big favourite here. It’s tactical enough to prototype but robust enough to incorporate into long term strategic architecture. It’s not super fast though so if your strategy is based on speed (i’m talking thousandth of a second making a big difference), you need something like C++.

For people wanting to pick up a language to help them get stuff done at work. Be careful if you think that C++/C# will help you more than say VBA. Most corporates have very strict IT policies which will prohibit you deploying or even developing anything in these languages. So unless your aim is to become a developer in their IT department or you only plan on working for very small shops with relaxed policies, be prepared to never make use of those skills. VBA however will let you fly under the radar of any IT policies and allow you to deploy your solutions to anyone who has Excel (which is pretty much everyone).

Interesting post, CleverCFA. I hadn’t thought about it that way.

I looked at python and know others like it. I didn’t like the forced indentations of source code myself, but perhaps I judged it too harshly. It feels like “the new BASIC,” which isn’t necessarily a bad thing. You can do a lot with BASIC.