Excel Macro to Balance Count and Average of Stocks with Positive and Negative % Change
Budget: $30 – $250 USD
I am looking for a macro that will run with the original file in step 1 all the way to step 6 in less than 2 minutes. The overall purpose of the macro is to select stocks that will give me as much of a perfect balance of count and average as possible. It would select the number of stocks to use on whichever (stocks with positive or negative change) has the lesser count and selecting that same count from the majority. I want the output to be put on a separate sheet only containing the stocks selected. Detailed step by step information below:
1. Notice column A (Ticker) and column F (Change %).
2. The macro will alter the % change whole number to a percentage. (e.g. 1.63 -> 1.63%)
3. Filter stocks that have an absolute change % between 1% - 20%. (>=1%,<=20%)
4. Find the count of each positive and negative % change that fits the criteria in step 3. In this example, there are 1853 stocks with positive change that fits the criteria in step 3. There are 1361 stocks with negative change that fits the criteria in step 3.
5. The macro would run to recognize that since there are fewer negative stocks than positive stocks that fit the criteria in step 3, then it will use the count of the negative stocks (1361) to select 1361 positive stocks to give an opposite average. If there were lesser positive stocks, than it would use that count to select from the pool of negative stocks.
6. Currently, the average of the 1361 selected negative stocks is -3.39%. So, excel needs to select 1361 positive stocks that will average as close to 3.39% as possible to give as much of a perfect balance of stocks with negative and positive % change as possible.
1. Notice column A (Ticker) and column F (Change %).
2. The macro will alter the % change whole number to a percentage. (e.g. 1.63 -> 1.63%)
3. Filter stocks that have an absolute change % between 1% - 20%. (>=1%,<=20%)
4. Find the count of each positive and negative % change that fits the criteria in step 3. In this example, there are 1853 stocks with positive change that fits the criteria in step 3. There are 1361 stocks with negative change that fits the criteria in step 3.
5. The macro would run to recognize that since there are fewer negative stocks than positive stocks that fit the criteria in step 3, then it will use the count of the negative stocks (1361) to select 1361 positive stocks to give an opposite average. If there were lesser positive stocks, than it would use that count to select from the pool of negative stocks.
6. Currently, the average of the 1361 selected negative stocks is -3.39%. So, excel needs to select 1361 positive stocks that will average as close to 3.39% as possible to give as much of a perfect balance of stocks with negative and positive % change as possible.