Forum Discussion
Smarter way to identify the top n in a list of stocks
Hi Can anyone suggest a smarter way to do this?
I am using Excel 2019
My Goal: Keep track of Stocks, ETFs and Mutual funds, based on dividends and an amount to invest.
I have a Database excel worksheet that keeps a list of stocks, dividends and notes
The price of the stocks is being updated automatically in real time.
Company | Ticker | Price | Type | Pay | Yearly Dist.Dec 2022 | M Dist. | Potential Shares | Monthly DIV | Projected Annual DIV | ROI | DIV TYPE | Notes |
For each category, (Stock/BDC, ETF and Mutual Funds) I have alphabetically set these by Ticker
When I enter an amount to Invest the Number of Potential shares and the Projected Annual Dividend is computed.
I then have another sheet that looks at the top 10 (including all ) by using
'=INDEX(Projected_Annual__DIV, MATCH(LARGE(Projected_Annual__DIV,1),Projected_Annual__DIV,0)), etc
To generate a table of the top 10 investment returns. Here I am referencing the database sheet for ALL entries.
Here is my question:
To find only Stocks and BDCs, I created another work sheet where I copied the data from the Database work sheet to this BDC ETF Stocks sheet.
To get a list of the top 10 in this category I use
‘=INDEX(BDC_Stocks, MATCH(LARGE(BDC_Annual_DIV,1),BDC_Annual_DIV,0))
(BDC_Stocks refers to stocks listed in this worksheet).
I do the same for the Mutual Funds.
Is there a smarter way to generate the top 10 by Dividend for each of these 3 categories
- ALL Stocks
- BDC/ETF and other Stocks
- Mutual Funds
Without having to copy the data for the last 2 to create new tables for indexing ? This is a problem if I start adding new stocks or funds to my list.
Would appreciate any suggestions, without having to write code
Thanks
5 Replies
- If you have Excel 365 then I would suggest you look at the SORT and SORTBY functions. Once you get it sorted you can use TAKE() to take the top x rows. You might also benefit from the FILTER function to pull the stocks you are interested in. While you're at it I recommend you learn LET() because that will help in lots of cases. There are a LOT of new functions but the above are probably the ones that you may want in this case.
Beyond that I would need more like sample sheets and such to better understand what exactly you are doing/needing- Unfortunately I do not have Excel 365.
All I am trying to do is make sublists of a Bigger list and then identify the top 10 rows of each sublist. I need to be able to add new elements to the Big List and have the sublists be updated accordingly
