none
Access, PowerQuery, or... RRS feed

  • General discussion

  • 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.

    == Cross-posted on the PowerQuery forum ==

    Saturday, November 7, 2015 1:26 PM

All replies

  • Yes.  PowerQuery and PowerPivot might work.  Try using Power Query to load the data into a PowerPivot model.  PowerPivot will store the data in a compressed format in memory. 

    This will work especially well if you can replace your calculated columns with Measures in PowerPivot, which are calculated on-the-fly instead of stored.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Saturday, November 7, 2015 2:04 PM
  • Thanks for responding David. 2 short follow-up questions if you don't mind:

    1. Since I want to re-use the data after storing it in PowerPivot I would probably need to use PowerView or something similar. But if I use such a report generator I could build the report on PowerQuery tables straight away, right? In other words I wouldn't need PowerPivot in between. Am I correct on this?

    2. Isn't all functionality I would use with PowerQuery, PowerPivot and for instance PowerView combined in Access? Using one integrated program sounds easier to me. Does the trio have an advantage over Access? Or is it the other way around?

    Thanks again!

    drftr

    Saturday, November 7, 2015 7:08 PM
  • I have reservations about whether Access is suitable for your application, but I don't think I have a good enough picture of what would be required in a single database to be sure.  However, you said something that is wrong, so I thought I'd better correct it.  You said:

    Access has VBA which is very powerful but it also has a 65,000 row limitation

    Access doesn't have any such limitation, not even in older versions;  I don't know where you got that idea.  Access does have a file-size limit:  an Access database file can be no more than 2GB in size.  But an Access table can have millions of rows.

    A limitation of Access that may come into play is that an Access table can have no more than 255 columns, and a query can return no more than 255 columns.  If you really need to show something like 2000 columns horizontally in a tabular format, I don't think Access is the program for you.  However, the 2000 calculated columns you refer to may be a result of Excel's spreadsheet orientation, and maybe there'd be a way to restructure your application to eliminate that layout.  Still the sort of massive, complex, interdependent calculations that Excel handles quite well may not be easy to implement in Access.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, November 8, 2015 12:06 AM
  • >In other words I wouldn't need PowerPivot in between

    PowerPivot is the data storage and calculation engine for your analytical model.

    >Using one integrated program sounds easier to me. Does the trio have an advantage over Access?

    PowerQuery, PowerPivot and PowerView are all part of Excel.  And they are also available in PowerBi Desktop

    Access is designed to build small applications, not to do data analysis.  PowerBi is designed from the ground-up to do end-to-end data analysis and business intelligence.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, November 8, 2015 2:55 AM
  • Got it - thank you David.

    Would I be able to query the measures that are stored in PowerPivot for further research, say finding the maximum value in a certain date range (rows) in one of the calculated scenarios (columns)? Or or they just results that can be read but can't be re-used?

    drftr

    Sunday, November 8, 2015 8:17 AM
  • Great answer Dirk - highly appreciated...

    Somehow I came across the 64 thousand row limit but when double-checking as a result of your comment I can't find it so I must have misinterpretated something.

    I could get around the 255 column limit by breaking up the Excel sheets and store the daily, weekly and monthly moving average columns in different tables. That would work. Funny thing is that the maximum number of working days (=trading days) is 254 so255 is plenty! ;-)

    Basically there's 3 kinds of calculations I need and I thought this wouldn't pose a problem for Access:

    Step 1. Calculate a moving average for stock prices of one stock and store all these multi-day moving averages in columns. So per row (for the date) a column for the 5 day calculated moving average on that date, the 10 day moving average, up to 255 days.

    Step 2. Calculate all moving average crossover points per date and store these in a table. This is where the column limit comes in as I want to calculate and store the return of a stock if the 5 day moving average crosses over the 10 day moving average (column 1), over the 15 day moving average (column 2) and so on. And in a different table 10 crossing over 15, 10 crossing over 20, etcetera.

    Step 3. Calculate in which scenario the stock price would have advanced most over different time-spans. For example a would want to calculate these results per decade and want to be able to see the growth of the stock price for all different moving average crossovers. There would be stock price growth if the lower moving average crossed over the higher moving average (as that would mean buying the stock), so I would have to calculate the difference between the price on the last day the lower moving average was above the higher moving average (the selling date of the stock) and the first day this was the case. Thess results should be stored as well, most likely in yet another table.

    Step 4. I would use a view / report to see the possible implications of all this on my investment strategy, either by automatically selecting the optimal moving average scenario (so the one with the highest return over a certain manually entered time-span) or by manually entering the moving average scenario I'd like to see the results of. Like: Show me the results for stock X over period Y using moving average crossover scenario Z.

    All of this would probably mean a set of tables for each individual stock plus some aggregated tables on which the reports are based. I absolutely have no experience to tell whether Access is strong enough for this stuff or that I should try my luck elsewhere.

    Thanks a million...

    drftr

    Sunday, November 8, 2015 8:47 AM