Excel Object Reliability

[Dana86]

Has anyone used Autoit with Excel for long periods of time? I do a lot of data science research & it's significantly easier to edit an excel spreadsheet & add new logic for some quick experimentation rather than having to edit a database. Most experimental models fail & less than 5% succeed, so it is significantly less soul-crushing to perform experiments in Excel. 

I've been communicating between VBA, Autoit, Python & using Excel as an experimental database. So far it's been a week and it has not crashed or run into any problems yet... What are some problems I should be aware of in terms of a long term Excel Autoit dynamic?  

Your wisdom is much appreciated! Thanks!😁

[RTFC]

I use AutoIt mainly in the context of (heavily numerical) scientific research, and frankly, I wouldn't touch Excel with a bargepole, for a number of reasons. Firstly, it imposes highly restrictive limits on the size and shape of data sets. Secondly, although internally, it uses 15-digit precision in calculations (binary double precision), once a workbook is saved, all accuracy beyond four decimal places is lost; plus, errors due to premature rounding are common, and difficult to trace. Furthermore, the provided statistics are weak, and graphs are low-res and more suitable for a business powerpoint presentation than scientific publication. The problem is that it tries/claims to be a jack of all trades, so it ends up doing most things poorly. I grant you that worksheet editing is quick and intuitive, so it's perfect for accounting and taxes and such like. But when I make data edits I need to be able to keep a track record, so I keep separate AutoIt scripts that perform explicit data preprocessing for me (plus a personal worklog text file to keep track of what I did when, and which script produces what (type of) output).

Of course I don't know what kind of modelling you do, or what your data sets actually look like. But if they can get large (>2GB), or if numerical accuracy or statistical significance is important, I wouldn't rely on an MS-Office tool to do science with. I'd say use a fast matrix/mathematical environment for computing with large datasets (I use Eigen, which is free (AutoIt wrapper library here, a dedicated statistics package for EDA and testing (I use Minitab (not free, but intuitive) and SAS (not at all intuitive), free version here), and a graphics package that allows you to control dpi for publishable figures (I mainly use gmt, also free; but may not be suited to your purpose).

Edited February 18, 2020 by RTFC

[Neutro]

Excel will become unresponsive, freeze and crash the bigger your data size becomes.

Autoit has natively embedded functions to deal with SQLite databases, you can look it up in its helpfile.

You can also find examples in the forum and around.

This might be good enough for your personal use. 

In the long run if more people could work on your data as well you may want to switch to a regular database system like MySQL or MariaDB but they require a server to run on.

But it's hard to tell precisely not knowing what data you're working on, from which sources and to what end

 

[jchd]

I'm very happy with the couple {Mathematica, SQLite}. Yes I know: while SQLite is powerful and free, Mathematica is fantastic but not exactly cheap. I particularly appreciate its language and the uncompared possibilities it offers to manipulate and represent data.

Yet if you're serious about modelling real-world data and behavior, you should consider investing in a serious tool.

[Dana86]

I'm using excel to store & calculate algorithms for a trained model. The data for training the model can hit 1 close to 12mbs... So far my prediction excel file is less than 1mb. So I think should be fine? Thanks! 

[Neutro]

Excel unresponsiveness = ((data size) x (operation complexity))² / computer performance

So in the end it all depends on how your data size and your algorithms complexity will evolve with time. If they stay somewhat around what you're dealing with now you should be fine since as you said in your first message: "it has not crashed or run into any problems yet" 

But if you expect them to grow in size and complexity excel will become more and more unresponsive until you can't use it at all anymore.

In that case i suggest you keep working with excel right now as you're familiar with it and it's working so far, but when you have some free time spend some of it to look into translating your current solution to using a real database system as stated previously.

 

[jchd]

While @Neutro answer is correct, I don't get your 12 Mb data -> 1 Mb .xls file, but that's unimportant.

More worrisome is the issue with Excel (or AutoIt, or C, or Fortran, whatever) handling of numeric data, particularly with evolving algorithms, when you can't easily back them up by a strong uncertainty calculation nor a fixed target precision (that is: I want the result of the following computation to be correct within +/- 1e-9).

It's indeed very easy to get completely wrong results using widely used languages/tools, seemingly simple algorithms and inconspicious real-world data, even with well established IEEE floating-point.
See for instance https://www.autoitscript.com/forum/topic/197061-floating-point-can-be-toxic/