Hi and thanks for reading my first post...
I need to make a decision about which tool to use for a project and could use your expert help. I have a bunch of financial files in Excel that are about 450MB per file, so temporarily about double when saving them. Undoable while travelling and working on a Windows based tablet as it takes 15 minutes to even open the files, let alone edit and save them. In these files I use downloaded stock prices from Yahoo Finance for which I calculate all kinds of moving averages and investment scenarios. The daily data is in 10,000 rows, the different moving averages (in steps from 5-300 days, plus 2-52 weeks, plus 1-12 months) in 150 columns. All these cells have Excel formulas in them to calculate the moving averages. Next to these 150 columns there's over 2,000 columns filled with IF-formulas that calculate crossover points between the moving averages, say, if the 20 day moving average crosses above the 80 day moving average. That's an enormous task and I find it incredible that Excel can actually do that. But...
Isn't there a better and faster way to do so? I have to do all sorts of find & replace tricks to update part of the formulas because I can't use even more nesting and stuff. I was thinking in the direction of Access or PowerQuery so I can load and rework all the data easily with a macro and then design the formulas once for all columns after which the software copies the results of those formulas (so not the formulas themselves like in Excel) for all rows.
Now my question is what tool would be best suited for such a project? Access has VBA which is very powerful but it also has a 65,000 row limitation which will be important for other projects. Furthermore I'm using Office 2013, Access 2013 is not for sale anymore, and when buying Access 2016 I have to upgrade the whole suite (while travelling long-term in Africa with terrible internet connections so it will take a week to download!). Would PowerQuery be a solition? Does it have the flexibility I have with VBA and SQL? Any limitations I might run into? Any other tools?
Your advice is highly appreciated!
Tnx, drftr.
I need to make a decision about which tool to use for a project and could use your expert help. I have a bunch of financial files in Excel that are about 450MB per file, so temporarily about double when saving them. Undoable while travelling and working on a Windows based tablet as it takes 15 minutes to even open the files, let alone edit and save them. In these files I use downloaded stock prices from Yahoo Finance for which I calculate all kinds of moving averages and investment scenarios. The daily data is in 10,000 rows, the different moving averages (in steps from 5-300 days, plus 2-52 weeks, plus 1-12 months) in 150 columns. All these cells have Excel formulas in them to calculate the moving averages. Next to these 150 columns there's over 2,000 columns filled with IF-formulas that calculate crossover points between the moving averages, say, if the 20 day moving average crosses above the 80 day moving average. That's an enormous task and I find it incredible that Excel can actually do that. But...
Isn't there a better and faster way to do so? I have to do all sorts of find & replace tricks to update part of the formulas because I can't use even more nesting and stuff. I was thinking in the direction of Access or PowerQuery so I can load and rework all the data easily with a macro and then design the formulas once for all columns after which the software copies the results of those formulas (so not the formulas themselves like in Excel) for all rows.
Now my question is what tool would be best suited for such a project? Access has VBA which is very powerful but it also has a 65,000 row limitation which will be important for other projects. Furthermore I'm using Office 2013, Access 2013 is not for sale anymore, and when buying Access 2016 I have to upgrade the whole suite (while travelling long-term in Africa with terrible internet connections so it will take a week to download!). Would PowerQuery be a solition? Does it have the flexibility I have with VBA and SQL? Any limitations I might run into? Any other tools?
Your advice is highly appreciated!
Tnx, drftr.