OK...I have a excel formula(s)/macro that I want to build and really have
gotten stuck on trying to finish it off. Here is why to what I would like
this thing to pull off:

I run this fanatsy sim hockey league with a friend...its a keeper league
that has contracts and drafts and free agency. Well, its the free agency
that consumes my time. This is what I'm trying to do...
1) we want to have a player randomly say what he is looking for (I have
gottent that piece).

2) we would like the player to go to a team that makes an offer randomly
(not quite there on this part, b/c of #3)

3) we would like to have percentages based on the teams' offer...meaning, if
a team makes a better offer then what the player is looking for, that the
team has better odds at picking him up. (I have how the odds increase or
decrease, based on contract length and amount).

Basically, What I have been doing in the past is taking the teams offers and
putting the teams into a hat, the better the offer, the more times there name
is in the hat.

So, if anyone has any ideas, I would love to hear them.
Thanks in advance
Cheers

This is an interesting study in micro-economics... See this link for a
complete discussion:

http://mason.gmu.edu/~tlidderd/104/ch3Lect.html

Let me pitch a few micro principles and my assumptions, then give an
overview of how I would approach the problem.

Free agent players are searching for better pay and are bidding salaries up.
Teams are searching for free agents, but want to control salary costs.
Every season there are a number of free agents, and a number of teams
searching for players. Free agents and teams will negotiate until an
equilibirum condition is reached. Each agent and each team will have an
opening bid and will modify the bid in successive rounds of negotiation.
Free agents will reduce their asking price and teams will increase their bid.
When the bid meets the price, a deal is struck between the free agent and
the team; the free agent and slot are removed from the pool and the
negotiation continues with the remaining pool of free agents/slots. The
process continues until the market clears... All free agents have been hired
by teams.

This will require a macro that loops until the market clears.

My approach:

Determine the starting position of each free agent. Probably their current
salary with a random percentage added. You can bias the percentage added
using performance criteria; better performance = higher starting position.

Determine the starting position of each team. You might want to use a
percentage of the team's median salary or maximum salary. You can also bias
the offer by factors such as bench strength or funds available for players
salaries.

Compare each salary offered by a team to each salary asked for by a player.
If the offer is greater than the salary asked for, you have a match and the
player is assigned to the team. Take the player and team out of the pool.

Once all the comparisons are made:

Add a random number to the salary bids from the teams. Biasing factors can
also be used for the increase... Teams with little extra discretionary funds
for players salaries will likely not increase their salary bid much.

Subtract a random number from the free agent's asking price. Biasing
factors can be used on the players as well. Better players will be more
reluctant to decrease their asking price.

After all the adjustments go back to the comparison step and see if any more
free agent/team deals have been made.

Continue looping until there are no more free agents and teams.

Regards,

ChristopherTri


"mslabbe" wrote:

OK...I have a excel formula(s)/macro that I want to build and really have
gotten stuck on trying to finish it off. Here is why to what I would like
this thing to pull off:

I run this fanatsy sim hockey league with a friend...its a keeper league
that has contracts and drafts and free agency. Well, its the free agency
that consumes my time. This is what I'm trying to do...
1) we want to have a player randomly say what he is looking for (I have
gottent that piece).

2) we would like the player to go to a team that makes an offer randomly
(not quite there on this part, b/c of #3)

3) we would like to have percentages based on the teams' offer...meaning, if
a team makes a better offer then what the player is looking for, that the
team has better odds at picking him up. (I have how the odds increase or
decrease, based on contract length and amount).

Basically, What I have been doing in the past is taking the teams offers and
putting the teams into a hat, the better the offer, the more times there name
is in the hat.

So, if anyone has any ideas, I would love to hear them.
Thanks in advance
Cheers

That was a long read...however, we were not trying to go that deep...what we
wanted to do is have the better offers for a player have greater the chance,
BUT not a definite match, meaning some players just want to play for certain
teams. We also are looking at it one player at a time...

Thanks for the read and suggestions...
Cheers

"ChristopherTri" wrote:

This is an interesting study in micro-economics... See this link for a
complete discussion:

http://mason.gmu.edu/~tlidderd/104/ch3Lect.html

Let me pitch a few micro principles and my assumptions, then give an
overview of how I would approach the problem.

Free agent players are searching for better pay and are bidding salaries up.
Teams are searching for free agents, but want to control salary costs.
Every season there are a number of free agents, and a number of teams
searching for players. Free agents and teams will negotiate until an
equilibirum condition is reached. Each agent and each team will have an
opening bid and will modify the bid in successive rounds of negotiation.
Free agents will reduce their asking price and teams will increase their bid.
When the bid meets the price, a deal is struck between the free agent and
the team; the free agent and slot are removed from the pool and the
negotiation continues with the remaining pool of free agents/slots. The
process continues until the market clears... All free agents have been hired
by teams.

This will require a macro that loops until the market clears.

My approach:

Determine the starting position of each free agent. Probably their current
salary with a random percentage added. You can bias the percentage added
using performance criteria; better performance = higher starting position.

Determine the starting position of each team. You might want to use a
percentage of the team's median salary or maximum salary. You can also bias
the offer by factors such as bench strength or funds available for players
salaries.

Compare each salary offered by a team to each salary asked for by a player.
If the offer is greater than the salary asked for, you have a match and the
player is assigned to the team. Take the player and team out of the pool.

Once all the comparisons are made:

Add a random number to the salary bids from the teams. Biasing factors can
also be used for the increase... Teams with little extra discretionary funds
for players salaries will likely not increase their salary bid much.

Subtract a random number from the free agent's asking price. Biasing
factors can be used on the players as well. Better players will be more
reluctant to decrease their asking price.

After all the adjustments go back to the comparison step and see if any more
free agent/team deals have been made.

Continue looping until there are no more free agents and teams.

Regards,

ChristopherTri


"mslabbe" wrote:

OK...I have a excel formula(s)/macro that I want to build and really have
gotten stuck on trying to finish it off. Here is why to what I would like
this thing to pull off:

I run this fanatsy sim hockey league with a friend...its a keeper league
that has contracts and drafts and free agency. Well, its the free agency
that consumes my time. This is what I'm trying to do...
1) we want to have a player randomly say what he is looking for (I have
gottent that piece).

2) we would like the player to go to a team that makes an offer randomly
(not quite there on this part, b/c of #3)

3) we would like to have percentages based on the teams' offer...meaning, if
a team makes a better offer then what the player is looking for, that the
team has better odds at picking him up. (I have how the odds increase or
decrease, based on contract length and amount).

Basically, What I have been doing in the past is taking the teams offers and
putting the teams into a hat, the better the offer, the more times there name
is in the hat.

So, if anyone has any ideas, I would love to hear them.
Thanks in advance
Cheers