Fantasy Football & Excel VBA

Hello All,

I have an application for fantasy football for which I'd like to use
Excel VBA.

First, you have to know a little about the setup.
I am joining a FREE auction league, for fun.
This league works a little differently than most leagues people here
may be involved with.

Each player has a certain "cost".
Each team owner (me) has a maximum amount he can "spend" on players.
Each player is projected to score a certain amount of points for the
year (my projections)
Each team owner is REQUIRED to have a specific number of players at
each position without going over the salary cap.

Here are those requirements.
Each team owner MUST have:
3 QB's (out of 50 choices)
5 RB's (out of 80 choices)
7 WR's (out of 100 choices)
2 TE's (out of 50 choices)
2 K's (out of 50 choices)
2 Def (out of 50 choices)

OK. I have made an excel sheet containing the name, cost, and projected
point total of every one of these players. What I'd like to do is cycle
through EVERY possible combination setup(yes...I know there would be
trillions of combinations) and check the following:

What is the total projected point value of the current possible team
combination?
What is the current cost of the current possible team combination?

Subject to the constraints:

If projected point value is less than the previously tested projected
point value, disregard combination.
If the cost of the current combination exceeds my salary cap, disregard
combination.

In the end, I'm of course looking for the most possible team points
(projected) without going over my salary cap.

I have a fair amount of excel VBA programming experience and I've
thought of these possible hangups regarding the code.

How to be efficient? It would be easy to write a code that ended up
testing the same combination many times, as you went through testing
loops. Given the number of combination, efficiency would be key.
How to set up a good looping structure given that positions 1-5 (of the
6 total) would be held stationary while position 6 was cycled and
combinations were tested. Then, one slight change would need to be made
in position 5 and position 6 would need to be cycled again. This would
occur until all of position 5 was cycled and then 1 slght change would
be made in position 4, etc.., etc...

I realize this will be quite a looping structure. If my math is right,
there are trillions and trillions of possible combinations. Is this
even practical? Can it work?

Do you actually want to list all 380 (by my count) players and their
ratings?

It seems to me that you can eliminate before listing all of those that cost
more but rate less.


"A Mad Doberman" <[email protected]> wrote in message
news:[email protected]...
> Hello All,
>
> I have an application for fantasy football for which I'd like to use
> Excel VBA.
>
> First, you have to know a little about the setup.
> I am joining a FREE auction league, for fun.
> This league works a little differently than most leagues people here
> may be involved with.
>
> Each player has a certain "cost".
> Each team owner (me) has a maximum amount he can "spend" on players.
> Each player is projected to score a certain amount of points for the
> year (my projections)
> Each team owner is REQUIRED to have a specific number of players at
> each position without going over the salary cap.
>
> Here are those requirements.
> Each team owner MUST have:
> 3 QB's (out of 50 choices)
> 5 RB's (out of 80 choices)
> 7 WR's (out of 100 choices)
> 2 TE's (out of 50 choices)
> 2 K's (out of 50 choices)
> 2 Def (out of 50 choices)
>
> OK. I have made an excel sheet containing the name, cost, and projected
> point total of every one of these players. What I'd like to do is cycle
> through EVERY possible combination setup(yes...I know there would be
> trillions of combinations) and check the following:
>
> What is the total projected point value of the current possible team
> combination?
> What is the current cost of the current possible team combination?
>
> Subject to the constraints:
>
> If projected point value is less than the previously tested projected
> point value, disregard combination.
> If the cost of the current combination exceeds my salary cap, disregard
> combination.
>
> In the end, I'm of course looking for the most possible team points
> (projected) without going over my salary cap.
>
> I have a fair amount of excel VBA programming experience and I've
> thought of these possible hangups regarding the code.
>
> How to be efficient? It would be easy to write a code that ended up
> testing the same combination many times, as you went through testing
> loops. Given the number of combination, efficiency would be key.
> How to set up a good looping structure given that positions 1-5 (of the
> 6 total) would be held stationary while position 6 was cycled and
> combinations were tested. Then, one slight change would need to be made
> in position 5 and position 6 would need to be cycled again. This would
> occur until all of position 5 was cycled and then 1 slght change would
> be made in position 4, etc.., etc...
>
> I realize this will be quite a looping structure. If my math is right,
> there are trillions and trillions of possible combinations. Is this
> even practical? Can it work?
>

Nahh...I don't want to actually list all the players. I'm really just
trying to get ideas on an algorithm that will cycle through all the
combinations and check them.

The auction system is set up so there are not very many players who
cost more but rate less, as you were saying. There are, however, many
tiers. There are many players who rate less, but cost less. So, for
instance, you might spend a large part of your salary cap on a QB, but
will have to sacrifice on RB and/or WR.

That's were the algorithm comes into play. Trying to find the absolute
best combination of players (based on my point projections), without
going over the salary cap.



David Cox wrote:
> Do you actually want to list all 380 (by my count) players and their
> ratings?
>
> It seems to me that you can eliminate before listing all of those that cost
> more but rate less.
>
>
> "A Mad Doberman" <[email protected]> wrote in message
> news:[email protected]...
> > Hello All,
> >
> > I have an application for fantasy football for which I'd like to use
> > Excel VBA.
> >
> > First, you have to know a little about the setup.
> > I am joining a FREE auction league, for fun.
> > This league works a little differently than most leagues people here
> > may be involved with.
> >
> > Each player has a certain "cost".
> > Each team owner (me) has a maximum amount he can "spend" on players.
> > Each player is projected to score a certain amount of points for the
> > year (my projections)
> > Each team owner is REQUIRED to have a specific number of players at
> > each position without going over the salary cap.
> >
> > Here are those requirements.
> > Each team owner MUST have:
> > 3 QB's (out of 50 choices)
> > 5 RB's (out of 80 choices)
> > 7 WR's (out of 100 choices)
> > 2 TE's (out of 50 choices)
> > 2 K's (out of 50 choices)
> > 2 Def (out of 50 choices)
> >
> > OK. I have made an excel sheet containing the name, cost, and projected
> > point total of every one of these players. What I'd like to do is cycle
> > through EVERY possible combination setup(yes...I know there would be
> > trillions of combinations) and check the following:
> >
> > What is the total projected point value of the current possible team
> > combination?
> > What is the current cost of the current possible team combination?
> >
> > Subject to the constraints:
> >
> > If projected point value is less than the previously tested projected
> > point value, disregard combination.
> > If the cost of the current combination exceeds my salary cap, disregard
> > combination.
> >
> > In the end, I'm of course looking for the most possible team points
> > (projected) without going over my salary cap.
> >
> > I have a fair amount of excel VBA programming experience and I've
> > thought of these possible hangups regarding the code.
> >
> > How to be efficient? It would be easy to write a code that ended up
> > testing the same combination many times, as you went through testing
> > loops. Given the number of combination, efficiency would be key.
> > How to set up a good looping structure given that positions 1-5 (of the
> > 6 total) would be held stationary while position 6 was cycled and
> > combinations were tested. Then, one slight change would need to be made
> > in position 5 and position 6 would need to be cycled again. This would
> > occur until all of position 5 was cycled and then 1 slght change would
> > be made in position 4, etc.., etc...
> >
> > I realize this will be quite a looping structure. If my math is right,
> > there are trillions and trillions of possible combinations. Is this
> > even practical? Can it work?
> >

I have grasped the essence of the problem, and can see commercial
possibilities in a solution. This is the sort of problem that is just up my
street,. I have written a commercial VB app that nearly optimises the path
of a PCB drilling machine. This made a $70,000 machine operate 15% faster
than when programmed by an "expert" programmer. The solution of such
problems relies on the method used to cut down the number of unwanted
comparisons.

I am sure that there are thousands of excel programmers that can write a
program to consider all of the possibilities, but you ain't gonna live that
long. I am trying to save those trying to help you from writing code that
will not have any practical use.

So, can we assume that you have eliminated all of the no-hopers?

Now I would want to produce columns like so:
Position _|_______________QB2____|___ RB5____ |____ WR7____ |etc

each position to have at least three columns:
estimated points, cost, estimated. points/cost

The estimated cost divided by the cost gives a bang per buck figure. I would
want this sorted in descending order, so that the calculations try the best
prospects first. This may be enough optimisation to make a solution in Excel
practical, although I am inclined to doubt it.

Anybody in the group got criticisms of this approach to an Excel solution,
or better ideas?

Meanwhile I am contemplating if my minimum path work can be adapted to this
problem, and how many people would be prepared to pay how much to get the
best selections from their point estimations.

David F. Cox





"A Mad Doberman" <[email protected]> wrote in message
news:[email protected]...
> Nahh...I don't want to actually list all the players. I'm really just
> trying to get ideas on an algorithm that will cycle through all the
> combinations and check them.
>
> The auction system is set up so there are not very many players who
> cost more but rate less, as you were saying. There are, however, many
> tiers. There are many players who rate less, but cost less. So, for
> instance, you might spend a large part of your salary cap on a QB, but
> will have to sacrifice on RB and/or WR.
>
> That's were the algorithm comes into play. Trying to find the absolute
> best combination of players (based on my point projections), without
> going over the salary cap.
>
>
>
> David Cox wrote:
>> Do you actually want to list all 380 (by my count) players and their
>> ratings?
>>
>> It seems to me that you can eliminate before listing all of those that
>> cost
>> more but rate less.
>>
>>
>> "A Mad Doberman" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello All,
>> >
>> > I have an application for fantasy football for which I'd like to use
>> > Excel VBA.
>> >
>> > First, you have to know a little about the setup.
>> > I am joining a FREE auction league, for fun.
>> > This league works a little differently than most leagues people here
>> > may be involved with.
>> >
>> > Each player has a certain "cost".
>> > Each team owner (me) has a maximum amount he can "spend" on players.
>> > Each player is projected to score a certain amount of points for the
>> > year (my projections)
>> > Each team owner is REQUIRED to have a specific number of players at
>> > each position without going over the salary cap.
>> >
>> > Here are those requirements.
>> > Each team owner MUST have:
>> > 3 QB's (out of 50 choices)
>> > 5 RB's (out of 80 choices)
>> > 7 WR's (out of 100 choices)
>> > 2 TE's (out of 50 choices)
>> > 2 K's (out of 50 choices)
>> > 2 Def (out of 50 choices)
>> >
>> > OK. I have made an excel sheet containing the name, cost, and projected
>> > point total of every one of these players. What I'd like to do is cycle
>> > through EVERY possible combination setup(yes...I know there would be
>> > trillions of combinations) and check the following:
>> >
>> > What is the total projected point value of the current possible team
>> > combination?
>> > What is the current cost of the current possible team combination?
>> >
>> > Subject to the constraints:
>> >
>> > If projected point value is less than the previously tested projected
>> > point value, disregard combination.
>> > If the cost of the current combination exceeds my salary cap, disregard
>> > combination.
>> >
>> > In the end, I'm of course looking for the most possible team points
>> > (projected) without going over my salary cap.
>> >
>> > I have a fair amount of excel VBA programming experience and I've
>> > thought of these possible hangups regarding the code.
>> >
>> > How to be efficient? It would be easy to write a code that ended up
>> > testing the same combination many times, as you went through testing
>> > loops. Given the number of combination, efficiency would be key.
>> > How to set up a good looping structure given that positions 1-5 (of the
>> > 6 total) would be held stationary while position 6 was cycled and
>> > combinations were tested. Then, one slight change would need to be made
>> > in position 5 and position 6 would need to be cycled again. This would
>> > occur until all of position 5 was cycled and then 1 slght change would
>> > be made in position 4, etc.., etc...
>> >
>> > I realize this will be quite a looping structure. If my math is right,
>> > there are trillions and trillions of possible combinations. Is this
>> > even practical? Can it work?
>> >
>

The brain ticks on.

Arranging the data as I have suggested leaves three possibilities.

1) The players top of the bang-per-bug ratings total cost is equal to the
budget - problem solved.

2) The total cost exceeds the budget. In this case you have to try swapping
players for those with lower costs.

3) The total cost is lower than the budget. In this case you have to try
swapping players for those with more points.

HTH


"David Cox" <[email protected]> wrote in message
news:[email protected]...
>I have grasped the essence of the problem, and can see commercial
>possibilities in a solution. This is the sort of problem that is just up my
>street,. I have written a commercial VB app that nearly optimises the path
>of a PCB drilling machine. This made a $70,000 machine operate 15% faster
>than when programmed by an "expert" programmer. The solution of such
>problems relies on the method used to cut down the number of unwanted
>comparisons.
>
> I am sure that there are thousands of excel programmers that can write a
> program to consider all of the possibilities, but you ain't gonna live
> that long. I am trying to save those trying to help you from writing code
> that will not have any practical use.
>
> So, can we assume that you have eliminated all of the no-hopers?
>
> Now I would want to produce columns like so:
> Position _|_______________QB2____|___ RB5____ |____ WR7____ |etc
>
> each position to have at least three columns:
> estimated points, cost, estimated. points/cost
>
> The estimated cost divided by the cost gives a bang per buck figure. I
> would want this sorted in descending order, so that the calculations try
> the best prospects first. This may be enough optimisation to make a
> solution in Excel practical, although I am inclined to doubt it.
>
> Anybody in the group got criticisms of this approach to an Excel solution,
> or better ideas?
>
> Meanwhile I am contemplating if my minimum path work can be adapted to
> this problem, and how many people would be prepared to pay how much to get
> the best selections from their point estimations.
>
> David F. Cox
>
>
>
>
>
> "A Mad Doberman" <[email protected]> wrote in message
> news:[email protected]...
>> Nahh...I don't want to actually list all the players. I'm really just
>> trying to get ideas on an algorithm that will cycle through all the
>> combinations and check them.
>>
>> The auction system is set up so there are not very many players who
>> cost more but rate less, as you were saying. There are, however, many
>> tiers. There are many players who rate less, but cost less. So, for
>> instance, you might spend a large part of your salary cap on a QB, but
>> will have to sacrifice on RB and/or WR.
>>
>> That's were the algorithm comes into play. Trying to find the absolute
>> best combination of players (based on my point projections), without
>> going over the salary cap.
>>
>>
>>
>> David Cox wrote:
>>> Do you actually want to list all 380 (by my count) players and their
>>> ratings?
>>>
>>> It seems to me that you can eliminate before listing all of those that
>>> cost
>>> more but rate less.
>>>
>>>
>>> "A Mad Doberman" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > Hello All,
>>> >
>>> > I have an application for fantasy football for which I'd like to use
>>> > Excel VBA.
>>> >
>>> > First, you have to know a little about the setup.
>>> > I am joining a FREE auction league, for fun.
>>> > This league works a little differently than most leagues people here
>>> > may be involved with.
>>> >
>>> > Each player has a certain "cost".
>>> > Each team owner (me) has a maximum amount he can "spend" on players.
>>> > Each player is projected to score a certain amount of points for the
>>> > year (my projections)
>>> > Each team owner is REQUIRED to have a specific number of players at
>>> > each position without going over the salary cap.
>>> >
>>> > Here are those requirements.
>>> > Each team owner MUST have:
>>> > 3 QB's (out of 50 choices)
>>> > 5 RB's (out of 80 choices)
>>> > 7 WR's (out of 100 choices)
>>> > 2 TE's (out of 50 choices)
>>> > 2 K's (out of 50 choices)
>>> > 2 Def (out of 50 choices)
>>> >
>>> > OK. I have made an excel sheet containing the name, cost, and
>>> > projected
>>> > point total of every one of these players. What I'd like to do is
>>> > cycle
>>> > through EVERY possible combination setup(yes...I know there would be
>>> > trillions of combinations) and check the following:
>>> >
>>> > What is the total projected point value of the current possible team
>>> > combination?
>>> > What is the current cost of the current possible team combination?
>>> >
>>> > Subject to the constraints:
>>> >
>>> > If projected point value is less than the previously tested projected
>>> > point value, disregard combination.
>>> > If the cost of the current combination exceeds my salary cap,
>>> > disregard
>>> > combination.
>>> >
>>> > In the end, I'm of course looking for the most possible team points
>>> > (projected) without going over my salary cap.
>>> >
>>> > I have a fair amount of excel VBA programming experience and I've
>>> > thought of these possible hangups regarding the code.
>>> >
>>> > How to be efficient? It would be easy to write a code that ended up
>>> > testing the same combination many times, as you went through testing
>>> > loops. Given the number of combination, efficiency would be key.
>>> > How to set up a good looping structure given that positions 1-5 (of
>>> > the
>>> > 6 total) would be held stationary while position 6 was cycled and
>>> > combinations were tested. Then, one slight change would need to be
>>> > made
>>> > in position 5 and position 6 would need to be cycled again. This would
>>> > occur until all of position 5 was cycled and then 1 slght change would
>>> > be made in position 4, etc.., etc...
>>> >
>>> > I realize this will be quite a looping structure. If my math is right,
>>> > there are trillions and trillions of possible combinations. Is this
>>> > even practical? Can it work?
>>> >
>>
>
>

http://office.microsoft.com/en-ca/as...246031033.aspx

"A Mad Doberman" <[email protected]> wrote in message
news:[email protected]...
> Hello All,
>
> I have an application for fantasy football for which I'd like to use
> Excel VBA.
>
> First, you have to know a little about the setup.
> I am joining a FREE auction league, for fun.
> This league works a little differently than most leagues people here
> may be involved with.
>
> Each player has a certain "cost".
> Each team owner (me) has a maximum amount he can "spend" on players.
> Each player is projected to score a certain amount of points for the
> year (my projections)
> Each team owner is REQUIRED to have a specific number of players at
> each position without going over the salary cap.
>
> Here are those requirements.
> Each team owner MUST have:
> 3 QB's (out of 50 choices)
> 5 RB's (out of 80 choices)
> 7 WR's (out of 100 choices)
> 2 TE's (out of 50 choices)
> 2 K's (out of 50 choices)
> 2 Def (out of 50 choices)
>
> OK. I have made an excel sheet containing the name, cost, and projected
> point total of every one of these players. What I'd like to do is cycle
> through EVERY possible combination setup(yes...I know there would be
> trillions of combinations) and check the following:
>
> What is the total projected point value of the current possible team
> combination?
> What is the current cost of the current possible team combination?
>
> Subject to the constraints:
>
> If projected point value is less than the previously tested projected
> point value, disregard combination.
> If the cost of the current combination exceeds my salary cap, disregard
> combination.
>
> In the end, I'm of course looking for the most possible team points
> (projected) without going over my salary cap.
>
> I have a fair amount of excel VBA programming experience and I've
> thought of these possible hangups regarding the code.
>
> How to be efficient? It would be easy to write a code that ended up
> testing the same combination many times, as you went through testing
> loops. Given the number of combination, efficiency would be key.
> How to set up a good looping structure given that positions 1-5 (of the
> 6 total) would be held stationary while position 6 was cycled and
> combinations were tested. Then, one slight change would need to be made
> in position 5 and position 6 would need to be cycled again. This would
> occur until all of position 5 was cycled and then 1 slght change would
> be made in position 4, etc.., etc...
>
> I realize this will be quite a looping structure. If my math is right,
> there are trillions and trillions of possible combinations. Is this
> even practical? Can it work?
>

That article is quite interesting. Thanks, David.



David Cox wrote:
> http://office.microsoft.com/en-ca/as...246031033.aspx
>
> "A Mad Doberman" <[email protected]> wrote in message
> news:[email protected]...
> > Hello All,
> >
> > I have an application for fantasy football for which I'd like to use
> > Excel VBA.
> >
> > First, you have to know a little about the setup.
> > I am joining a FREE auction league, for fun.
> > This league works a little differently than most leagues people here
> > may be involved with.
> >
> > Each player has a certain "cost".
> > Each team owner (me) has a maximum amount he can "spend" on players.
> > Each player is projected to score a certain amount of points for the
> > year (my projections)
> > Each team owner is REQUIRED to have a specific number of players at
> > each position without going over the salary cap.
> >
> > Here are those requirements.
> > Each team owner MUST have:
> > 3 QB's (out of 50 choices)
> > 5 RB's (out of 80 choices)
> > 7 WR's (out of 100 choices)
> > 2 TE's (out of 50 choices)
> > 2 K's (out of 50 choices)
> > 2 Def (out of 50 choices)
> >
> > OK. I have made an excel sheet containing the name, cost, and projected
> > point total of every one of these players. What I'd like to do is cycle
> > through EVERY possible combination setup(yes...I know there would be
> > trillions of combinations) and check the following:
> >
> > What is the total projected point value of the current possible team
> > combination?
> > What is the current cost of the current possible team combination?
> >
> > Subject to the constraints:
> >
> > If projected point value is less than the previously tested projected
> > point value, disregard combination.
> > If the cost of the current combination exceeds my salary cap, disregard
> > combination.
> >
> > In the end, I'm of course looking for the most possible team points
> > (projected) without going over my salary cap.
> >
> > I have a fair amount of excel VBA programming experience and I've
> > thought of these possible hangups regarding the code.
> >
> > How to be efficient? It would be easy to write a code that ended up
> > testing the same combination many times, as you went through testing
> > loops. Given the number of combination, efficiency would be key.
> > How to set up a good looping structure given that positions 1-5 (of the
> > 6 total) would be held stationary while position 6 was cycled and
> > combinations were tested. Then, one slight change would need to be made
> > in position 5 and position 6 would need to be cycled again. This would
> > occur until all of position 5 was cycled and then 1 slght change would
> > be made in position 4, etc.., etc...
> >
> > I realize this will be quite a looping structure. If my math is right,
> > there are trillions and trillions of possible combinations. Is this
> > even practical? Can it work?
> >