[stealofhome]

This is how I run the top Free Agent report using Excel. First I configure the database export: Replace accents, Insert column names, players_basic, Show real player ratings, players_player_value. Then export to csv.

Then I open up the players and players_value files and paste these formulas at the end of the sheet (beginning at location CU1).

Position: =IF(D2=1,VLOOKUP(E2,[Fixes.xlsx]DraftList!$K$2:$L$4,2,0),VLOOKUP(D2,[Fixes.xlsx]DraftList!$H$2:$I$10,2,0))
Name: =F2&" "&G2
Age: =I2
FA: =IF(CW2<=22,0,Q2)
Career_value: =IF(ISNA(VLOOKUP(A2,INDIRECT("players_value.csv!$A $2:$S$"&COUNT(players_value.csv!$A:$A)-1),16,0)),0,VLOOKUP(A2,INDIRECT("players_value.csv !$A$2:$S$"&COUNT(players_value.csv!$A:$A)-1),16,0))

The VLOOKUP command under position references two tables that translates the "position" and "role" values for each player into an actual position name. The position value are the typical baseball values for 1-9 (Pitcher = 1, etc). If a player is listed as a pitcher, he will then have a role of 11 (SP), 12 (RP), or 13 (CL). I don't like to differentiate between RP and CL, so I just name them both RP.

Once the values are calculated, I sort by descending career value and filter on FA=1 and use that as the basis for the top 30 FA. This is not entirely accurate since the top FA once signed do not always follow the career value trend. I haven't tried yet to see which other variables are important for determining which FA will make the most money any given offseason. I imagine it has to do with expected performance, player personality, injuries, etc. This does fine for now.