Analyzing your team using MS Excel - Fantasy Baseball Cafe 2014

## Analyzing your team using MS Excel

Moderator: Baseball Moderators

I have an excel macro that will open up each day's yahoo roster for each team and copy/paste each active player's H/AB. IP is updated daily in the innings limit page so all I have to do is plug in the numbers and I can get weekly AVG, ER, and Baserunners. The rest is simply subtracted from last week's standings.

We use OBP so I came up with a little macro that will calculate that as well. I take hits and AB and continually add 1 to each until I reach the OBP of the team. It will also subtract an AB and start over if there's a sacrifice fly in there. As the season goes on and those numbers get bigger, the calculation becomes less precise, but I figured that it would not go beyond perhaps 12 plate appearances difference. I wish Yahoo! calculated OBP and WHIP into the 4th and 3rd digits, respectively. It would make these calculations much better.
Nerfherders
Minor League Mentor

Posts: 669
(Past Year: 5)
Joined: 25 Jun 2004
Home Cafe: Baseball

Yoda wrote:
roninmedia wrote:I just do a simple cut and paste and let excel automatically rank my performance in a series of categories and assign the respective point values like in roto. (#1 in half the categories, #2 and #3 in two more, 5th in one, and last in the final one). Time to move to improve that struggling area.

This works well for roto leagues. This works for my head to head points league, because I can see what certain categories I am slumping in and what I can do revamp against my opponent for that respective week and calculate chances of success. This is working out well in defining the strategy changes in drafting for H2H pts vs. any form of roto.

Yep. Also, if you play in the same league year after year, you can get the average of the leaders of each category. This way, come draft day, you know what type of production you need to win each category.

First year, so I am grasping the strategy using whatever statistical information I learned from fantasy football. Roto is something I have to learn and at the moment, i know the statistical analysis for baseball >>> football.
roninmedia
Major League Manager

Posts: 1647
Joined: 16 Jul 2003
Home Cafe: Baseball

MashinSpuds wrote:This is a great idea. What do you do for roto, though?

In my weekly league, I have it set up with 3 sets of rows -- Goal for each period, actual, and difference for each of the scoring cats. Goals are from the target numbers I set at draft time -- based on my projections of what should come in third in each category. I keep a separate sheet for hitting and pitching.

At the end of each week, I enter the actual data. If the 'difference' column is mostly black, it was a good week.

I can sum the differences to date and see if I am on pace in each cat and by how much.
Be excellent to each other.
Grouperman941
Major League Manager

Posts: 2079
Joined: 2 Jan 2004
Home Cafe: Baseball
Location: Rays Country

Is there any way to have excel do the rotisserie points system on 1 week's worth of stats? If there is could someone tell me how to do it?

Jbyrdo
College Coach

Posts: 317
Joined: 19 Jun 2005
Home Cafe: Baseball

I use Excel to help me out in my Roto league - for my pitchers.

If you play with a max innings limit, some people are at different levels of usage with their innings. I basically copy and paste the standings with the overall numbers of stats into a spreadsheet. I then take all the counting stats like W, K, Saves, Holds, and divide each total by the number of innings pitched for each team.

e.g.

Wins / Number of innings pitched = Rate of wins

This basically tells you how efficient your innings have been used and where you project to finish at the end of the season in terms of rankings in each category.

I basically use this to keep my mind at ease about where I am in the standings. Even though I may not be doing extremely well in terms of the counting stats in pitching, it sometimes turns out that relative to the number of innings I have used, I'm doing pretty well.
RynMan
Hall of Fame Hero

Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

If your good with excel you can do some advanced sorting and put in some if statements and some nested ifs and excel can realllllllllly put out some interesting information.
BigZ38
Minor League Mentor

Posts: 717
(Past Year: 1)
Joined: 19 Mar 2006
Home Cafe: Baseball

RynMan wrote:I use Excel to help me out in my Roto league - for my pitchers.

If you play with a max innings limit, some people are at different levels of usage with their innings. I basically copy and paste the standings with the overall numbers of stats into a spreadsheet. I then take all the counting stats like W, K, Saves, Holds, and divide each total by the number of innings pitched for each team.

e.g.

Wins / Number of innings pitched = Rate of wins

This basically tells you how efficient your innings have been used and where you project to finish at the end of the season in terms of rankings in each category.

I basically use this to keep my mind at ease about where I am in the standings. Even though I may not be doing extremely well in terms of the counting stats in pitching, it sometimes turns out that relative to the number of innings I have used, I'm doing pretty well.

I do this as well with wins, k's and saves...works wonders for planning strategy.
The Loveable Losers
Hall of Fame Hero

Posts: 7290
Joined: 30 Mar 2005
Home Cafe: Baseball
Location: Cubs Win!!! Cubs Win!!!

You can go to "Data" and then "Import External Data" to import the data from any web page into Excel. I like this better than copy and pasting.

I use the stats from Yahoo to create my own player rankings.

Because the Yahoo rankings are based on the standard 5x5 categories, and my league uses a 6x6 with different categories, I import the stats from Yahoo and then create my own top 100.

It works great. I am able to see how valuable players really are in our system and swing some sweet deals.
Don't try to strike everyone out. Strikeouts are boring besides that they're fascist. Throw some ground balls it's more democratic. - Crash Davis
Rookies
Softball Supervisor

Posts: 76
Joined: 17 Mar 2004
Home Cafe: Baseball
Location: Arizona

Hobbes wrote:I do this and I just copy and paste the weekly H2H matchup results for all of the teams. I can then sort each week's data by each category to see where I fell compared to all twelve teams in each category. So even if I've won runs for the week against my opponent, I might be tenth overall compared to all teams.

It can certainly help show where your true weaknesses are in a H2H league where you normally only see how you compare to one opponent each week.

This exactly what I do. I also look at each team to identify any hot or slumping players in thier lineup and make a notation to signal a slight inflation or deflation in a team's numbers.

I go into every week with a nice "scouting report" on my opponent. In leagues that make weekly lineup changes, this would be invaluable for setting your lineup. Since we have daily changes it isn't as much of a resource, but it gives me an idea of whether I should be starting more Runs/Sbs types or going for HRs/Rbis early in the week to match up better.

Copy and paste is easy and if you know excel at all, you can take it further by setting up equations to show different stats etc. You can use this same template year to year so it is worth the time setting it up in my opinion. Or just add a little each year.
JakeTrain72
Minor League Mentor

Posts: 643
Joined: 8 Feb 2006
Home Cafe: Baseball

JakeTrain72 wrote:
Hobbes wrote:I do this and I just copy and paste the weekly H2H matchup results for all of the teams. I can then sort each week's data by each category to see where I fell compared to all twelve teams in each category. So even if I've won runs for the week against my opponent, I might be tenth overall compared to all teams.

It can certainly help show where your true weaknesses are in a H2H league where you normally only see how you compare to one opponent each week.

This exactly what I do. I also look at each team to identify any hot or slumping players in thier lineup and make a notation to signal a slight inflation or deflation in a team's numbers.

I go into every week with a nice "scouting report" on my opponent. In leagues that make weekly lineup changes, this would be invaluable for setting your lineup. Since we have daily changes it isn't as much of a resource, but it gives me an idea of whether I should be starting more Runs/Sbs types or going for HRs/Rbis early in the week to match up better.

Copy and paste is easy and if you know excel at all, you can take it further by setting up equations to show different stats etc. You can use this same template year to year so it is worth the time setting it up in my opinion. Or just add a little each year.

How exactly do you copy all that info in? I usually cut and paste from Yahoo, but it puts the whole weeks results into ONE cell. I've also tried to use DATA-IMPORT DATA, but it brings up limited info and says I need to login. Any suggestions?
Chicago RedSox
Major League Manager

Posts: 1436
Joined: 30 Mar 2005
Home Cafe: Baseball
Location: SanDiego

PreviousNext

### Who is online

Users browsing this forum: unioreimi and 6 guests

Forums Articles & Tips Sleepers Rankings Leagues

Today's Games
Wednesday, Sep. 17
(All times are EST, weather icons show forecast for game time)

 • LA Dodgers at Colorado(3:10 pm)
 • San Francisco at Arizona(3:40 pm)
 • Toronto at Baltimore(7:05 pm)
 • Boston at Pittsburgh(7:05 pm)
 • Washington at Atlanta(7:10 pm)
 • Miami at NY Mets(7:10 pm)
 • NY Yankees at Tampa Bay(7:10 pm)
 • Cincinnati at Chi Cubs(8:05 pm)
 • Cleveland at Houston(8:10 pm)
 • Chi White Sox at Kansas City(8:10 pm)
 • Detroit at Minnesota(8:10 pm)
 • Milwaukee at St. Louis(8:15 pm)
 • Seattle at LA Angels(10:05 pm)
 • Texas at Oakland(10:05 pm)
 • Philadelphia at San Diego(10:10 pm)