SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 17th, 2016, 2:56 am

Opinions welcome. Plenty online re; shortcomings, new directions and alternatives. Besides being useless for MC, it's a workhorse.
 
User avatar
Cuchulainn
Posts: 60471
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

The future for Excel VBA

March 17th, 2016, 7:54 am

VBA and a future?I thought it was in permafrost. Why not just C#, Excel DNA??
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik
 
User avatar
tagoma
Posts: 18276
Joined: February 21st, 2010, 12:58 pm

The future for Excel VBA

March 17th, 2016, 8:15 am

With much humility, I second Monsieur Cuchulainn on this. I have never been a VBA fan and I personally tend to write python UDFs for my spreadsheets, eg using xlwings.
 
User avatar
Cuchulainn
Posts: 60471
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

The future for Excel VBA

March 17th, 2016, 8:28 am

Quote Besides being useless for MC, it's a workhorse. What you can do is write the MC in C (create a dll) and then call it from VBA/Excel. Which is a not all too bad IMO. But one should keep VBA at bay I suppose.
Last edited by Cuchulainn on March 16th, 2016, 11:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik
 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 17th, 2016, 1:07 pm

QuoteOriginally posted by: CuchulainnQuote Besides being useless for MC, it's a workhorse. What you can do is write the MC in C (create a dll) and then call it from VBA/Excel. Which is a not all too bad IMO. But one should keep VBA at bay I suppose.Re; dll - exactly what I've done in the past. Worked like a dream.I guess VBA's future lies in Microsoft's intentions - read somewhere that VBA will no longer be supported. .NET was supposed to be it's replacement. R and Python are also essentially freeware - I wonder where corporate tech policies will head.Programming education is another item. Quant Fin = C/C++, Data Science = R and Python, Finance = VBA???
 
User avatar
terrorbyte
Posts: 265
Joined: July 14th, 2002, 3:00 am

The future for Excel VBA

March 17th, 2016, 2:27 pm

Hmmmmm, this kind of speaks to a greater issue imo. The use of Excel and therefore the use of VBA. Excel is a great Swiss Army knife. It gets you into trouble but also solves a lot of problems. Right tool for the right job.For example, I still have not yet seen as good a reporting tool as Excel. Users (including myself) have found Crystal, SRS etc pallid imitations. So, VBA behind a spreadsheet to belch MTM and Var Reports, by pulling data from the corporate risk system, provides standardization and also the ability for users to flip the data into additional ad hoc reports that help them troubleshoot figures or generally provide user specific position, financial reports.I also like it as a prototyping tool for logic and maths. You can walk users through a heap of complex calcs, using Excel and VBA. Get their sign off and build a robust version in an enterprise language.I would be bereft if VBA disappeared. * Stands by and waits to be flamed * :-)
 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 17th, 2016, 3:42 pm

QuoteOriginally posted by: terrorbyte * Stands by and waits to be flamed * :-)Here it comes - a lot of Excel s/s's seem to be prototypes that didn't make it into a IT development stage. Also firms don't seem to bother with 'standards' re; creating s/s's, good VBA coding and documentation. Excel (+VBA) is a great tool for one-off analyses.VB your cleansing ale of choice Terrorbyte???
Last edited by dweeb on March 16th, 2016, 11:00 pm, edited 1 time in total.
 
User avatar
terrorbyte
Posts: 265
Joined: July 14th, 2002, 3:00 am

The future for Excel VBA

March 17th, 2016, 5:54 pm

Dweeb,re: Prototype that didn't make it into development. Below the belt blow but just soooooooo true!!!!!! I can't argue with it. :-) re: Standards. Nothing irritates me more than being sent someone else spreadsheet that "stopped working suddenly" and you open it up to find an absolute dog's breakfast of spaghetti coding. Damn it! 2 for 2!And I am giving you +3 additional points for "cleansing ale of choice" on St Patrick's Day.You got me! :-)
 
User avatar
Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

The future for Excel VBA

March 17th, 2016, 6:43 pm

Perhaps something like this may be an evolution (perhaps with Azure being replaced with an in-house solution, otherwise -- due to regulatory constraints -- I don't think this would work for everyone):http://blog.revolutionanalytics.com/201 ... excel.html
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

The future for Excel VBA

March 17th, 2016, 9:20 pm

Microsoft has started incorporating R into SQL Server and have bought Revolution R which is now renamed to Microsoft R so I don't think it's completely out of the question that the next Excel would include it as a runtime alternative to VBA. I'd say 70% chance.
 
User avatar
bearish
Posts: 5158
Joined: February 3rd, 2011, 2:19 pm

The future for Excel VBA

March 18th, 2016, 12:03 am

My take on this question is a little different. Few people posting in the P&S Forum are qualified to have an opinion on VBA because you think nothing of creating a project in Visual Studio, writing efficient code in C++ or C# to solve a problem, and jumping through the ExcelDNA or XLW hoops to create an addin. There are millions of people (at the risk of sounding like Carl Sagan, but I don't think I am exaggerating) who are just about able to augment their workbook with a bit of VBA code, most likely subroutines based on recording spreadsheet actions, who wouldn't have the first idea of how to install VS, let alone figure out what to do with it. From that starting point, there is a very long way to go, including figuring out how to write, debug and test functions, before having any consideration of stepping outside of the native Excel bubble. For this type of user, I think VBA is much better than nothing, which would seem to be the most likely alternative.
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

The future for Excel VBA

March 18th, 2016, 12:20 am

bearish I agree, I absolutely hate VBA and Excel but when people around me use it to solve a specific problem in front of them I'm okay with it but generally label it as "tactical" with hopes of replacing said solution with a "strategic" solution at a later date (where as proper IT scream and yell, yet give timelines measures in months or years to replace said working solution).The main strength is that Excel includes the "IDE" and the runtime natively which isn't the case for other options. If there was a built in C#, F# or PowerShell IDE and you could natively embed said code in the spreadsheet things might be a bit different.
 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 18th, 2016, 1:00 am

VBA also provides an alternative to nested Excel functions - functions within functions within functions (they exist). It's relatively easy to follow what's going on in VBA. Haug's - Complete guide to Option pricing Formulas VBA is a great way to study the code logic behind quant models.Data science (stats and CS) seems to everywhere these days. Stanford's - Elements of Statistical Learning - data mining, inference, and prediction - provides a great framework for the subject. But executing this in code - be it R, Python etc - arguably imposes an opportunity cost of learning and remembering syntax and semantics etc.Stats/econometrics/Data Science almost becomes cerebral after a while the more one studies it. One day we will be able to strap on a virtual reality headset and just think code. But in the meantime there's VBA, R, Python. C/C++, C#, Java.....
Last edited by dweeb on March 17th, 2016, 11:00 pm, edited 1 time in total.
 
User avatar
dweeb
Topic Author
Posts: 142
Joined: July 11th, 2009, 8:10 pm

The future for Excel VBA

March 18th, 2016, 1:30 pm

QuoteOriginally posted by: terrorbyte And I am giving you +3 additional points for "cleansing ale of choice" on St Patrick's Day.Interesting that a VBA expert's beer of choice is VB, aye.
Last edited by dweeb on March 17th, 2016, 11:00 pm, edited 1 time in total.
 
User avatar
terrorbyte
Posts: 265
Joined: July 14th, 2002, 3:00 am

The future for Excel VBA

March 18th, 2016, 1:55 pm

;-)
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