Which programming language to learn for an accountant going forward?

[sal11235]

Hi guys

I've searched on the forum and have found variations of this question answered but not exactly what I need. So I'll try and phrase my question clearly and see if people in the know can give me some good advice.

I am a newly qualified accountant, just getting into the swing of Excel VBA. I can knock up basic code and am getting more comfortable with debugging and coding on the fly, but am way off from being a competant VBA user. I am a keen Excel user and am learning skills everyday that are helping me to become the point of reference for all things Excel related in my department.

I envisage my role becoming more technical as I tinker with more complex spreadsheet models and would like to undertake some formal training in this regard (which my company will pay for!)

I know that VB6 is no longer being offered as a product by MS.

Question: From what you know about the future of VBA, is it worth me, as an accountant, to really dive into VBA or do you think that learning VBA.Net would be a shrewd move?

I say accountant to convey to you that I'll be using VBA primarily to create/change spreadsheet models with financial data in the excel front end.

I obviously want my education to help me with current problems but also be future proof, with an eye to further career opportunities.

Any advice would be greatfully received, many thanks
Sal

 

[Johnny C]

Hi Sal
I qualified as an accountant in 1995, and have been using VBA (even when it was Excel 4.0) ever since.

You can't go wrong with vanilla VBA to start with, but VBA.Net might open more doors for you.

What you will find is that more and more companies want not just Excel VBA, but VBA with Access to integrate the two, or VBA and SQL Server also to integrate spreadsheets into fully fledged databases. VBA.Net would be better for the SQL Server type roles.

The amount of data shovelled into spreadsheets (especially now they have 1,000,000+ rows per sheet) is appalling, add to which Excel's processing capabilities are quite limited with those volumes of data. If you know how to put it into even an Access Db, you can make huge tasks that would chew up a typical dual-core desktop for an hour or more into quick and painless ones with a couple of simple Access queries.

A year or so ago I had a contract where they had a spreadsheet with circa 100K rows on several spreadsheets from an input file. The spreadsheet that 'dealt' with it had 10 layers of pivot tables analysing the tables and integrating it. Apart from it taking 20mins to calculate every time you changed an cell, it would crash irrecoverably once a day, so much so that the first thing I did was to add a macro to the spreadsheet to replicate itself as soon as data was populated so there was always a 'virgin' copy.

However rather than rewrite it using Excel, it was clearly a task for Access which could execute 3 very simple queries and get a result in 10 secs. The girl who ran the spreadsheet could use icons and the File menu but had problems with pivot tables, vlookups even were things she had nightmares about, and when she was told it was going into an Access database was off sick with stress for 2 days.

So, I wrote it such that she didn't even know Access was on the PC; it loaded the data to Access tables, ran the queries and extracted the output of the final ojne and some simple vlookup-type queries on a hidden sheet. All she needed to do was type in 2 dates, press a button and the whole lot ran for her (and even created a powerpoint slide of the output for her).

The point of that is that those sort of skills are highly saleable.

Most users (even in finance functions) have very limited Excel skills (though they think they're the bees knees). And when you mention 'database' to them they pee their pants and go hide in a cupboard until you've gone. They know what excel they learnt in their first year with it and have learnt little since. I can remember people squealing like stuck pigs when they found menus were going and ribbons replacing them in XL2007, and I know quite a few who are happy to admit they can do less now that they could with menus. So the mention of 'database' strikes fear into their very cores.

The ability to create a database and integrate it seamlessly so the user doesn't even know it's being used is a very useful skill.

And if you can use SQL Server with VBA - well there are very few qualified accountants who can.

 

[sal11235]

Didn't think I'd get a response, checked back now and have been pleasantly surprised!

Thank you so much for taking the time to write that, it's really given me some ideas to chew on. It's not easy treading that line between being an accountant and a techie but that's what I'm going for. I still want to be valuable in a traditional finance function but add more value with the Excel skills. Thanks again

 

[xenou]

It's good to know some SQL and basic database theory - you can do some work with a desktop database like Access when analysing or storing data, and in some cases you can do some of your own reporting off an enterprise database if you have some SQL skills. As far as programming languages go, learn the one that's closest to you (often, VBA is immediately available and easy to implement in files you use everyday - viz., Excel). The main thing is doing as much programming as you can, and the best way to do that is with what's right at hand. You can always branch out or transition to another language. Most important is knowing the fundamentals of programming concepts and structures.

But if working with Excel is part of your everyday work, make sure you understand Excel without VBA too - many good VBA Excel programs leverage Excel's unique features, and don't try to do it all in pure VB only.

ξ

 

[sal11235]

Many thanks for the advice. After having considered it I think I'm happy to hone my VBA(6) skills for the moment. And Xenou, totally agree with you about the Excel skills. Those are never gonna go out of fashion in Finance. People always need on the fly assistance on formula tweaking and spreadsheet optimisation.

Thing is, I generally work in large finance functions where there are already people on the periphery of the department there who have an in depth knowledge of Access and SQL.

But my FC will come to me if he needs to solve something using SUMPRODUCT, or if he needs to streamline a report that is coming out of the BI tool. That is where I've added value up to now.

At the moment I'm working on the trading floor at a bank. The number of VBA automated reports in use is mindboggling. I just can't see, and no one else here for that matter, any seismic shift to .NET or whatever in the next few years. It's all done using VBA and there are no plans to phase this out. It's just not worth the time and effort. The reports do what needs to be done, quickly and effectively. And, just as importantly, with VBA a reasonably intelligent person can follow what's been done.

I qualified as an auditor from a big five firm recently, and have spent the last 3 years working in group finance in a multitude of companies. All of them used complex Excel spreadsheets with VBA automation. None of them had any plans to change to .NET or any other system.

What Microsoft can do about this I don't know, but from my experience I can see that companies are just saying, "if it aint broke, don't fix it"

 

[Johnny C]

it's not just a case of 'if it aint broke dont fix it' Sal, I've worked for large investment banks too and the VBA/Spreadhseet has become an integral part of mission-critical 'systems'.

It's more of a case that they would need to completely re-engineer the way they work and rebuild everything from scratch.

Given the turnover of staff in banks, that's not possible because no-one really knows how the whole lot hangs topether; it's fine knowing how a couple of spreadsheets hang together on your desk and understanding the VBA but you don't always know the VBA in other models elsewhere that supplies data, for example (in banks) pricing data which might come from VBA models in Risk, from Exchanges, from internal pricing models elsewhere.

I had to build a Swaps valuation model which was simple enough, but it took interest/exchange rate data using VBA from other models which I had no control of, I didn't know who owned them or looked after them and given the size of the operation it wasn't really possible to find out. I just had to trust they worked and make sure whatever I did didn't mess with them. There were other people downstream on the swaps trading desks using models that hooked into mine, I didn't know who they were and I doubt they knew who I was.

Whilst it was technically possible to replace the lot, it was such a tangled mess that it wasn't feasible. When people's million dollar bonus's depend on things being right you don't mess with the models that provide them with data, one tiny error and you'll be out of a job and black-balled from getting a job in a bank again.

 

[obiron]

I would suggest that Excel VBA and raw SQL would be a good place to start.

Access is not always available on desktops and is still a paid for product, but SQLExpress is free and supremely powerful (go figure...). Build forms and reports in Excel but store the data in SQL and learn how to move data from one to the other using OPENROWSET() and Excel ODBC links.

The SQL suite of products, whichever version you go for is the way forwards in a large organisation and you should be an evangelist for it.

SSRS (reporting Services) has some fantastic tools and allows reports and spreadsheets to be served up over the web.
SSIS (integration services) makes it simple to collate and import data from a number of sources, even if the quality is pretty ropey.
SSAS (analysis services) allows you to generate enormous pivot table-like OLAP cubes
SQL 2005 and 2008 has some pretty awsome features such as Common Table Expressions, Ranking functions and Hierachy traversing which are simply not possible in Excel (or not without some pretty heavyweight VBA)
Add to this the fact that jobs can be scheduled to be run by the server, rather than by users and the whole technology stack is pretty hard to beat.

Oracle and IBM DB2 offer similar technologies, but in my experience they are not as good (and do not have as stronger user community) as the Microsoft solutions.

If you then dump on top of SQL the .NET Platforms for Windows and Web sites and Silverlight for web based apps, you will be a real top end developer.