My bet is you are a retired engineer. Electrical? Mechanical? Or Aeronautics? You seem to constantly pick the wrong tools. Cannot build your own PC but want to learn Matlab? As said, learn Python, you will not regret it and its super easy to pick up. And don't even think how to interface with Excel or VBA regarding data sets. Just export the data from Excel into a csv file (google how to do it, its very easy) and Python lets you easily load a text file with data into a numpy array in Python. Excel and VBA are the wrong tools.
You would be surprised how easy it is to build a PC these days. It's comparable to LEGO. Everything comes in easy to assemble pieces. The best part is, there are hundreds of videos on how to do it. If you get a top end mother board. You'll only need to switch out your processor every time you want an upgrade. I recently found out the financial industry still leans heavy on EXCEL/VBA. I have seen some amazing spreadsheets built using VBA but IMO, do you really want to spend all that time learning something that is not at the forefront? Think about 10 years down the road? You can always post your code/sheet here. I am being forced to learn a bit of VBA so posting would be beneficial for the both of us. I took a MATLAB course in school. It was my intro to com sci and I loved it. But it's expensive and their MathWorks forum is not comparable to the Python,R,C# community. No problem. I will send you the bill in the mail.
Thanks. I know how to download and convert csv into xlsx, so should be able to do the reverse. I will definitely take a Python class on Coursera. MATLAB is too difficult so I am dropping the class. Coursera is wonderful, it is free if I just want to audit instead of taking for credit. Regards,
Ironchef, you probably don't need a new machine. Despite some of the answers you have got on this thread, you can probably parallelize your Excel calculations and achieve a speedup linear in the number of cores on your machine. The easiest (for e.g. 4 cores) would be to use xargs to spawn four copies of your workbook with different ranges specified as command line parameters, write the outputs to separate csv files, and on completion cat the four csv output files into a single sorted csv. That said, your problem is probably in your code. Post your excel worksheet so other posters can take a look at the code for you. That is a good idea. Python is better suited to your task than is excel/vba. And if you are already programming in VBA, you'll pick up Pyton quickly, This is a bad idea. Learning Matlab will get you thinking in terms of matrices and vectors (in terms of linear algebra). I can't emphasize enough how important that will be to you.
Too embarrass to disclose what I did in my prior life. I too often picked and tried the wrong stuffs but hell, I am willing to try any that I thought was of value including many harebrained ideas on ET. I sincerely thank you for your help. This is why I am hanging around.
Thank you for the wonderful suggestion of splitting up the computations. I will give it a try. As for computation and code, let me just describe what I am attempting to do instead of showing my amateurish codes: Two way to start my computation: 1. I have 25 years of historical equity daily data: open, high, low, close, adjusted close and volume. 2. I have the options in the code to also generate 25 years of equity data using a random number generator (same open, high...) using lognormal process. I have plan to also inject some non random perturbations. Next, I calculate, for each day, the HV using different moving average, compute IV based on certain assumptions, I then compute a series of option prices using Black Scholes. The reason I am doing this? I have too much time to kill. Kidding aside, I am practicing my coding skill, I am also curious about the randomness of various underlying and option prices. OK, I will go back and finish my MATLAB, after I finish my Python class. Best regards,
I found quite a few repeat calculations in the program I can group together to reduce computation steps. I can now complete each run in 1/4 of the time. Will try next to split up the run and parallel the computations. You guys saved me $5K (on a MacPro). Thanks a million.
I think Python should be able to do everything you need. Here is a $12 Udemy Python course which teaches how to do Linear Algebra for Data Science & Machine learning in Python. There's one more day at this price then you'll have to wait for another sale. Here is a Linear Algebra with Python page. Here is another: Linear Algebra with Python -- Vectors. — Steemit. For your Python installation, I think the Anaconda Python Distribution is the best and easiest to maintain. Be sure to download the macOS Python 3.7 64 bit graphical installer. It already includes the Numpy, SciPy, Scikit-learn and many other packages. Python is multi-core capable. If necessary, you could even use CUDA with Python. Anaconda package lists. Here is an Anaconda PDF manual. There are included packages which enable Python to directly read Excel files. The problem with Matlab is you must pay for many different toolboxes which aren't free. Even with the Home Edition, if you purchase only a few toolboxes, you could end up spending hundreds of dollars. Also, if you are trying to learn both Python and Matlab, you are dividing your time among the two. Python seems to be the language everyone is turning to today. You should have plenty of support if you need it.
Code: If you can post your code (with dummy code, if needed, protecting your secret sauces) in a code window, we may be able to help further. You can google how to improve the performance of excel vba code, but what I mainly do is: Split the problem up (if possible) and run multiple instances of Excel (Windows). No external software needed. Just make copies of the workbook with slightly different names, and start each while pressing the ALT key. Google: do you want to start a new instance of excel Disable screen updating as much as possible (rely more on the status bar). Don't forget to re-enable at code end or when code stops with an error. To the extent possible/feasible, transfer the sheet into an array (Not one cell at a time!), and perform calculations on the array in VBA rather than in the spreadsheet. (Doing this will get you used to coding as it would more likely be done in a standard language. For maximum speed, the sheet would only hold values, no formulas. All of the calculations would be done in VBA.) Via VBA, convert as many formulas as possible to plain values before running looping codes. Reconvert to formulas at end of code. Good Luck! Keep us Posted!