Looking for advice on the best way to summarise data..

[richardcarter]

Just looking for some 'best practice' advice on summarising a huge amount of data...

Quite simply, I have a huge worksheet which I use for analysing stocks. Column A shows Stock Names, Column B shows yesterdays prices, Column C shows todays prices, Column D shows % change on yesterday's price etc etc. The worksheet is updated several times a day with a click of a button (to refresh prices etc).

At present, if I want to see what today's top 20 gaining stocks are, then I normally just sort Column D in ascending order using a filter... easy enough.

However, with some 5,000 rows and hundreds of columns, the time it takes Excel to sort and filter is taking quite a few seconds - and its becoming extremely inefficient for me to work this way - especially that I also use filters and sorting columns to produce other similar 'summaries' too.

Ideally, what I would like to have is a simple summary table that is always kept up to date (so for example, a table on a separate sheet that will always display the current top 20 % gaining stocks without me having to do anything other than just clicking on that sheet!

I know a lot of people might suggest using pivot tables.. but apart from hating using pivot tables and the fact that they slow down my PC considerably. I really don't mind spending some time coding some vba or formulas because the end result will be well worth the effort. I am even wondering whether I should even be using Excel for this.. or whether it is time to move on to learning C++ or using Matlab/Mathworks (which I don't really want to do)... How would you tackle this? The only 'criteria' I have for this, is that it must be fast, efficient, and completely automatic.

Look forward to any comments

 

[Akashwani]

Hi Richard,

I'm not an expert, so I couldn't possibly advise on the best way to deal with this, personally, I'd have to create an array formula to "extract" the data I wanted and hope that it could cope with 5000 rows and hundreds of columns, which it probably wouldn't!!

Have you looked at Powerpivot?....

PowerPivot | Microsoft BI

MrExcel explains it here....

PowerPivot Data Analyst 1 - What is PowerPivot? - YouTube

Good luck with this and now that your post has been "bumped up", hopefully someone else will read it and offer some better advice.

Ak

 

[Jerry Sullivan]

Hi all,

Another option is to setup a QueryTable that uses MSQuery to return the Top 20% of records based on the "% change" column.

Both PowerPivot and MSQuery use SQL to extract records meeting criteria. MSQuery is a No-Frills tool compared to PowerPivot; but since this is a relatively simple query, it's a good option to consider.

A query that would return the data you describe is...
SELECT TOP 20 PERCENT *
FROM MyData as D
ORDER BY D.[% change] DESC

You could completely automate updates of the QueryTable using VBA to:
1. Update a Named Range (MyData) that refers to your data whenever it gets changed.
2. Refresh the query upon clicking on the sheet with the Top 20% Table

Richard, If you haven't used MSQuery and want some step by step instructions, please identify which version of Excel you are using.

 

[richardcarter]

Many thanks to both of you for the suggestions. I wasn't aware of PowerPivots at all - and on the surface, could be the answer to all of my problems! I downloaded and installed it over the weekend and had a play around with it after looking at some tutorials... although I think it's going to be a while before I get up to speed with it properly... so maybe this could be the best solution for me in the longer term.

I would certainly like to investigate MSQuery too.. this could be the short term solution.. so would like to take you up on the offer of some step by step instructions.. or at least be pointed in the direction of a good starting place. I am using Excel 2010. Many thanks!

 

[Jerry Sullivan]

Here are step-by-step instructions on how to make a query table for your purpose using xl2010.

It assumes you have already defined a Named Range for your stock data called "MyData".
The Named Range must reference a fixed range (it cannot be a Dynamic Named Range).
It further assumes there are headers in the first row of the range and the column of interest has the header "% change"

The titles in blue font below represent the titles of the dialog windows that you'll see as you proceed.

Save and Close the Workbook with the stock data range Named "MyData".

Open a NEW blank workbook

From the Ribbon:
Data> From Other Sources > From Microsoft Query

Choose Data Source
Excel Files* > OK > (Browse to your file with the stock data and select it) > OK

Query Wizard - Choose Columns:
Select the Name "MyData" then click the ">" button to select its fields
Next >

Query Wizard - Filter Data:
Click the cancel button to shortcut wizard steps that are not needed
You'll see a prompt "Do you want to continue editing this query in Microsoft Query?
Click Yes

A Microsoft Query Window will open
Click on the menubar button labeled "SQL"

Copy and Paste this text into the SQL statement: box (replacing the existing statement).

SELECT TOP 20 PERCENT *
FROM MyData as D
ORDER BY D.[% change] DESC

You'll see a prompt: "SQL Query can't be represented graphically. Continue anyway?
Click OK
Click on the menubar button with the Exit Door and arrow

Import Data
Select the Option Button for Table
Click OK

That should import your table. Once this is setup, your table just needs to be refreshed to import the latest data.

Optionally, you can Copy and Paste the Table into the same workbook as your stock data.

First confirm that this table gives you the desired results and that you can update the table by Right-clicking the Table > Refresh

Once that works, the final part would be automating the Refresh and the redefining of your Named Range if it will change in size.
Both of those are relatively simple to do with VBA event code.

I don't know how much experience you have with VBA. Just ask if you want some help with that code.

 

[richardcarter]

Many thanks for the instructions... and very much appreciated!