Is VBA still worth learning?

[d08]

Use the best tool for the job. If your trading style isn't limited by Excel and VBA why switch? Finance is basically just one giant spreadsheet. To be honest, I mostly program as a means to collate data. My stuff isn't anything an Excel sheet and a little elbow grease couldn't do. In fact I mostly prefer to use Excel.



Migrate the chunks you need out of Excel. If you pick up C# you can write addons to help you offload stuff to "outside the sheet" in something a little lower level than VBA.

Again, Excel is absolutely fine. Work around it until you absolute can't/won't - then look for something else. Python has it's own problems - dynamic typing means you don't have an IDE that can discover types and 15,000 lines later you'll look back and wonder what you're even passing in. Believe me, I work professionally in Python and have written a handful of Python projects around finance. It is a great language to replace Perl as a prototyping language but it is absolute garbage for anything at scale (for a more practical example - look at the problem dropbox is having and how they've basically had to hack type annotations and type checking onto Python in order to continue to use it).

EDIT:

To address your question on VBA directly - it will not dramatically improve the speed of your sheet. It is better than using formulas but you're still using Excel as a VM. If speed is a concern considering writing Excel addons to do what you need or offload data processing/collating to a language better suited for it and only load in the refined data into Excel for final processing/study.

That's the whole point of Python, to speed up development. Type hints work fine and if you want to enforce them then compile with Cython. I know, I know, "it's not python" but it might as well be.

Excel is far too unstable for any serious work. I used IB's ActiveX many years ago and it froze a few times and the code was often broken after a new API version. The GUI mixed with code is a big mess and it was very cumbersome.

Python isn't too slow for any kind of trading you'll do on a retail account. I run my stuff from a VPS that's in NYC while connecting to IB's Chicago servers and even then my cumbersome code it's fast.

On top of that you can develop on Windows and run the code on Linux. Windows for an automated trading machine is a very poor decision and should be avoided. You'll also enjoy avoiding platform fees if you're a cheapskate like me.

 

[d08]

I finally figured out how to program in VBA Excel. So far, that is all I need.

That said, I do have a question: How do I program Kalman Filter algorithm using VBA? Or are there existing subroutines I can use?

I think I asked that before but forget the answer. This has nothing to do with trading, I am dreaming up a different project that might need best estimation going forward.

Thanks guys.

This is why you'd use Python. You could use a library and do that with ease, not ask around about it on ET.

 

[d08]

VBA is mostly restricted to the WinDOHs universe. Outside of that, it is useless. I strongly suggest getting into Python. It is cross platform and very extendable, and very simple to learn the basics. Even a basic GUI is easy, with the right tools, such as tkinter or pyqt. Alternately, go in for C. Not C#, C. Or C++, whatevah. Locking yourself into the mockrosoft cage could possibly limit you and what you can do at some point in the future.

Back in the day I used to do some VB programming, starting with VB3, then VBDOS and VB6. Never messed with VBA. Never saw the reason, and I had to be careful not to write anything that would not run under OS/2 or Linux, for much of that time.

C is much too cumbersome for any modern day "simpler" usage. C# problem is as you said, locking yourself onto their platform.

PS. PySide2 over PyQt, much better license.

 

[gaussian]

C# problem is as you said, locking yourself onto their platform.

On what planet are you platform locking yourself in C#? Do you work from 2007? .NET Core has been out for YEARS now and allows full Microsoft-backed cross platform compatibility. Mono has also made HUGE strides (though it's still stuck in WinForm land, but I guess that's not a bad thing!)

Excel is far too unstable for any serious work. I used IB's ActiveX many years ago and it froze a few times and the code was often broken after a new API version. The GUI mixed with code is a big mess and it was very cumbersome.

This is conjecture. Excel is the single most popular tool in Finance eclipsing use of Python many-fold. It is fine for it's purpose and I've done plenty of serious work in it. The benefit is you can have someone else audit your sheet because everyone knows Excel.

I consider "automated trading" outside the realm of a retail in general. You're just build a commission generating system for your broker. Unless you're on a DMA brokerage you're wasting time AND money.

Windows for an automated trading machine is a very poor decision and should be avoided.

This is such a tired and bad opinion. I do all of my trading on Windows, most developers in the WORLD do all their development on Windows and they, nor I, and have had a problem. Windows 10 has made amazing leaps in usability for "modern" dev work, including adding a POSIX emulation layer. Microsoft is spearheading a massive open source movement and contributing incredible code to the community. They purchased Github and maintain the (current) most popular code editor on the planet for all platforms - Visual Studio Code.

I could make the same argument for POSIX systems.

OS X - Massive platform lock, expensive hardware, walled garden.

Linux - Walmart variety everything. Piss-poor graphics drivers (CUDA is BARELY supported on major distributions), more hardware control in exchange for less reliability when you need it. Strange threading model. The only thing REALLY going for Linux is the efficiency of "everything being a file" which is why it makes an excellent server hypervisor.

I work professionally from a company macbook. I'd rather be using a Windows 10 laptop. I run all my servers in Linux because kubernetes lets me not care about the underlying hypervisor.

 

[d08]

On what planet are you platform locking yourself in C#? Do you work from 2007? .NET Core has been out for YEARS now and allows full Microsoft-backed cross platform compatibility. Mono has also made HUGE strides (though it's still stuck in WinForm land, but I guess that's not a bad thing!)

Did not know about that but it seems it's still far from a smooth experience. I still prefer languages that are completely indifferent about OSs.

This is conjecture. Excel is the single most popular tool in Finance eclipsing use of Python many-fold. It is fine for it's purpose and I've done plenty of serious work in it. The benefit is you can have someone else audit your sheet because everyone knows Excel.

I consider "automated trading" outside the realm of a retail in general. You're just build a commission generating system for your broker. Unless you're on a DMA brokerage you're wasting time AND money.

Notepad is also the most popular text editor despite the fact EditPlus (in Windows) is superior in every way. Does it mean Notepad is better? I'm not seeking for the most popular tool, I'm seeking for the tool that works best for the task at hand.

Regarding automated trading, that's just your opinion and has nothing to do with reality. I'm fully automated as are some others on ET. Unless you mean HFT type trading, in which case I'd agree.

This is such a tired and bad opinion. I do all of my trading on Windows, most developers in the WORLD do all their development on Windows and they, nor I, and have had a problem. Windows 10 has made amazing leaps in usability for "modern" dev work, including adding a POSIX emulation layer. Microsoft is spearheading a massive open source movement and contributing incredible code to the community.

I could make the same argument for POSIX systems.

OS X - Massive platform lock, expensive hardware, walled garden.

Linux - Walmart variety everything. Piss-poor graphics drivers (CUDA is BARELY supported on major distributions), more hardware control in exchange for less reliability when you need it. Strange threading model. The only thing REALLY going for Linux is the efficiency of "everything being a file" which is why it makes an excellent server hypervisor.

I work professionally from a company macbook. I'd rather be using a Windows 10 laptop. I run all my servers in Linux because kubernetes lets me not care about the underlying hypervisor.

Just today I had a "fun" Windows experience. The GFs laptop had an app uninstalled after a big update, this happened automatically without the users' knowledge. It has happened before a couple years back. Sure, on a trading PC you'd have auto-updates disabled but taking such action without users' knowledge is still absurd.
If you think this is acceptable behavior then we have nothing in common.

Most servers and robust systems run on Unix based systems, most definitely not Windows. I've seen enough BSODs on Windows ATMs, airport screens etc. in my life, I'd never trust Windows to run something critical.

What the coders use to code I couldn't find any official stats on but most on the higher level definitely do not use Windows.
The time required to get a decent compiler to run on Windows is ridiculous and on a couple of PCs I haven't been able to update the required components at all. In Linux it takes seconds. There's no comparison. Windows 10 is an okay system for basic browsing and gaming but for anything more serious, you definitely want Linux.
OS X is mediocre at best and I'd never lock myself into an Apple world, it's highly restrictive.

 

[gaussian]

Just today I had a "fun" Windows experience. The GFs laptop had an app uninstalled after a big update, this happened automatically without the users' knowledge. It has happened before a couple years back. Sure, on a trading PC you'd have auto-updates disabled but taking such action without users' knowledge is still absurd.
If you think this is acceptable behavior then we have nothing in common.

Hasn't been my experience ever on a Windows machine. The only gripe I have is the forced restarts, but you can disable that trivially. This sounds a lot like PEBKAC and is being blamed on the OS because you have some vendetta against Windows.

What the coders use to code I couldn't find any official stats on but most on the higher level definitely do not use Windows.

Define higher level? A study in 2010 shows Windows being dominant in all languages but Javascript. This took 2 seconds to Google. Windows is the single most popular business operating system. Game developers are some of the most talented engineers in the industry and virtually all of them program on Windows (DirectX is so much better than OpenGL they aren't even on the same plane of existence anymore). I'd consider myself both high level (as I am a consultant people are willing to pay top dollar for) and experienced and aside from a few niche cases majority of my B2B clients are using a Microsoft ecosystem. When you need guarantees you don't use an operating system maintained by volunteers (Linux).

The reason I use Windows is the developer experience is entirely integrated and seamless. Visual Studio just works, the C# test frameworks just work, etc. I do not have to trouble myself with `virtualenv` or any other environment management framework. When things just work I am more productive - and to be honest that's really all I care about.

Windows 10 is an okay system for basic browsing and gaming but for anything more serious, you definitely want Linux.

I've written everything from data analysis tools to compilers on Windows and I've never had a problem with it. On the contrary the second a package manager fails (even when I used Slackware religiously) it ended up in a multi-day wild goose chase. Windows doesn't have these problems because things just work. This trope that "Linux is for serious developers" is absolute garbage. Linux is a good operating system for the niche it fills - fully owning your operating system from the bootloader up (through the virtues of GNU). It is not any more "performant" than Windows in the present decade. You may have had an argument for Windows 95 and Windows 98 SE but since XP Microsoft has done an excellent job at keeping up with modern operating systems. I fully support open source and owning your hardware (I love the coreboot project and run it on my laptops when the hardware allows), but I am not deluded enough to think Linux is somehow more performant by virtue of it being Linux. Especially given the massive effort Microsoft has undertaken to modernize Windows 10 after the Windows 8 disaster.

Businesses using Linux servers (in my experience) are running some variation of RHEL which is a pay-to-play version of Linux (where things just work) because as I said above you don't want volunteers maintaining the backbone of your infrastructure. It is, at this level, a matter of preference. The upside of using Linux on the server side is the highly configurable nature of it - and to be honest with Powershell I'm sure Windows Server is just as configurable - I just have never had to use it. Again your arguments hold no water because any company worth their salt is running a Kubernetes cluster and the underlying hypervisor is literally "pick your favorite flavor" because they all run docker instances equally well. The only salient point of your argument is licensing fees, but those are not an issue if you run inside of Azure or own your own rack.

 

[GrowleyMonster]

Popularity of an OS or apps that run on it is mostly driven by marketing, not by quality or security.

 

[d08]

Hasn't been my experience ever on a Windows machine. The only gripe I have is the forced restarts, but you can disable that trivially. This sounds a lot like PEBKAC and is being blamed on the OS because you have some vendetta against Windows.

This is very common and affects ALL users, nothing to do with PEBVAC. Also, some updates require restarts, they're not applied without one. Not heard of this in Linux. I've been a Windows user all my life because I thought the absurdities were normal -- they are not.

https://betanews.com/2015/11/24/windows-10-uninstalling-user-programs-without-permission/

Define higher level? A study in 2010 shows Windows being dominant in all languages but Javascript. This took 2 seconds to Google. Windows is the single most popular business operating system. Game developers are some of the most talented engineers in the industry and virtually all of them program on Windows (DirectX is so much better than OpenGL they aren't even on the same plane of existence anymore). I'd consider myself both high level (as I am a consultant people are willing to pay top dollar for) and experienced and aside from a few niche cases majority of my B2B clients are using a Microsoft ecosystem. When you need guarantees you don't use an operating system maintained by volunteers (Linux).

The reason I use Windows is the developer experience is entirely integrated and seamless. Visual Studio just works, the C# test frameworks just work, etc. I do not have to trouble myself with `virtualenv` or any other environment management framework. When things just work I am more productive - and to be honest that's really all I care about.

Well, in Linux, at least in 2019 things just work. Not in Windows. But for games I agree, Windows is still superior.

I've written everything from data analysis tools to compilers on Windows and I've never had a problem with it. On the contrary the second a package manager fails (even when I used Slackware religiously) it ended up in a multi-day wild goose chase. Windows doesn't have these problems because things just work. This trope that "Linux is for serious developers" is absolute garbage. Linux is a good operating system for the niche it fills - fully owning your operating system from the bootloader up (through the virtues of GNU). It is not any more "performant" than Windows in the present decade. You may have had an argument for Windows 95 and Windows 98 SE but since XP Microsoft has done an excellent job at keeping up with modern operating systems. I fully support open source and owning your hardware (I love the coreboot project and run it on my laptops when the hardware allows), but I am not deluded enough to think Linux is somehow more performant by virtue of it being Linux. Especially given the massive effort Microsoft has undertaken to modernize Windows 10 after the Windows 8 disaster.

When I needed to compile something on Windows, it's always been a pain. Visual Studio required with 4GB of downloads to follow. Now if you have a crappy connection, this can take days. Why is it that I can run the same thing on Linux within a few minutes?
I'm not someone who has a hard-on for Linux, I've openly criticized it a lot in the past but nowadays, it's just better for most things

Linux is much MUCH better with resources. Recently I noticed that my Windows machine with 8GB of RAM is constantly struggling even when I'm not using much at all. RAM usage is dramatically lower in Linux.
Microsoft thinks it's smart to keep OfficeClickToRun and Edge in memory while I'm trying to keep usage down to run VMs. With every update, there's more garbage sitting in memory, stuff that I will never use.

 

[BrooksRimes]

I use VBA with Microsoft Access and do consulting in Access. Often users start something in Excel and it becomes so large and/or complex that it becomes a good candidate for an Access upgrade. Access is Microsoft's desktop database and is included in Microsoft Office Professional version. You can create very user-friendly screens and reports in Access. (SQL Server is Microsoft's enterprise level database) I created an option backtester in Access (similar to Tradestation or Amibroker) because there was no product on the market at that time. VBA is the macro language behind all of the Microsoft Office products.

 

[tsgiannis]

I use VBA with Microsoft Access and do consulting in Access. Often users start something in Excel and it becomes so large and/or complex that it becomes a good candidate for an Access upgrade. Access is Microsoft's desktop database and is included in Microsoft Office Professional version. You can create very user-friendly screens and reports in Access. (SQL Server is Microsoft's enterprise level database) I created an option backtester in Access (similar to Tradestation or Amibroker) because there was no product on the market at that time. VBA is the macro language behind all of the Microsoft Office products.

It makes me laugh when i see people struggling with Excel ....they push it..and they push it...and they add features...but in the end is just a spreadsheet...nothing more ...nothing less...minus the charts which is a marketing decision...Access is a much better tool...