Monday, December 22, 2014

Yahoo Fantasy Football API Using Python

UPDATE: the YQL module can no longer be installed directly using PIP. You have to install it from pip using the GitHub repository. Check out this Stack Overflow post on how to get er done:

Game Changer...after about 8 hours of trying to figure out how to use Yahoo's Fantasy Football API I finally made some huge progress. I was able to use Yahoo's 3-legged OAuth method to authenticate to my Yahoo Fantasy Football league. From there it was just a matter of figuring out how to traverse the various JSON responses in order to consume the data.

This post assumes the following:

  • You have Python 2.7 installed
  • You know how to add modules to Python using easy_install or pip
  • You have some general knowledge of moving around in Python
Holler if you have any problems or if you have a specific question.

Authentication and Sample Scripts

OAuth Steps

I had to setup a few things before getting started. I am hitting the yahoo API using YQL which is Yahoo's Query Language. You can try out the YQL Console here. I used the console often to test various queries. There are several samples of queries hitting various APIs.

YQL Module Oauth Update 12/1/2015

The YQL Python module I was using is no longer maintained. The dev does have it out on GitHub for the time being. I had some SSL certificate trouble trying to install the module using Python 2.7.10. Therefore I just downloaded the ZIP from GitHub and then used pip to install the module from the local repository using this command:

(Make sure you are in the root of the repo folder)
pip install -e .

Make sure you include that period on the end of the command

From there I was able to continue with the Yahoo OAuth Process.

You will need to create a project to get a API Consumer key and Consume Secret. Once you login to Yahoo Developer network you can go to the projects page: Here you can create a new app.

Sample Python code for Authentication

You setup a simple cache directory where your authentication token will be stored. If your token is expired the script will request another one. On the initial execution of this script you will need to allow your new application to access your yahoo account data.

When you run the script in Python you will be prompted to visit a URL. This URL will then spit out a code. You take the code and paste it back in your Python Console. This will setup a trust with your new application. You only have to do this to establish the token. Subsequent calls should work fine.

From there you start writing your YQL queries.

Now its just a matter of looping through the JSON response and grabbing the data you need. Here is a full Python Script example.


It took a while for me to figure out how to traverse the various JSON responses from the API. Once you get in there and mess around for a bit it will get a little easier. I targeted the following data sets of interest to me. There are other data sets available. You can use the YQL console to browse around and grab what you want.


Using the teams API I was able to capture all the teams in the league. The teams are given a unique team_key that will be used later when we go to find rosters and such. 
  • Team_key: unique ID for each team 
  • TeamName: name of the team given by the manager of the team
  • Division: Division ID the team belongs to.
  • Number_of_Moves: Number of player adds/drops
  • Number_of_Trades: Number of player trades
  • Manager_Nick: Nick Name of the manager in their yahoo account
  • Manager_Email: Email address of the manager
Stat Settings

The Stat Settings table is used to store information about the various scoring categories. Each stat has a unique stat_id. I used the LeagueSettings API to grab this data.

  • Stat_ID: Unique ID identifing each stat
  • Enabled: bit value indicating if the stat is enabled
  • stat_name: name of the stat (Example: Passing yards)
  • Stat_modifier: How much the stat is worth. (Example: In our league a passing yard is worth 0.05 points)
I need to figure out how to get the bonus settings. I may just hard code that since I was not having much luck grabbing that from the API.


The matchups text file shows the result of each week's head to head matchup. I stored the Team ID to enable me to join that to the team table detailed above.

  • Week Number: Week of the matchup
  • Matchup ID: I number each matchup for SELECT purposes. There are 2 teams for every matchup ID.
  • Team ID: unique team identifier
  • Team Points: Number of points the team scored
  • Team Projected Points: Number of points the team was projected to score.
  • Team Key: Another Unique team identifier.
Roster Stats

For each team's roster stats I execute a terribly inefficent loop where I grab a team id, then hit the API using a WEEK IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17). I grab the stats for every time for each week and dump in into the text file. There are two outputs from the script.

  • Roster
    • Team ID: Unique Team Identifier
    • Fname: Player First Name
    • Lname: Player Last Name
    • Team: Player's Team name (Ex: Kansas City Chiefs)
    • Player ID: Unqiue player identifier
    • Player Key: Unqiue player Identifier
    • Week Number: Season Week Number
    • Total Points: Points scored by player based on our league settings
    • Position: Roster position
  • Roster stats
    • Player ID: Unique player identifier
    • Week Num: Season Week Number
    • Player Key: Unique Player identifier
    • Stat ID: Stat identifier (Ex: Passing Yards)
    • Stat Value: Value of the stat (Ex. 314)

Now I just have to wait until tomorrow when the season is complete. I will do one last data grab and then the real fun can start. I plan to start analyzing the data. I am especially interested in draft analysis to determine the impact of various drafted players on the season, rate how well each manager drafted, etc. I'd also like to play with various scoring scenarios to see how that effects league scoring.

Our league is unique in the fact that we have a 2QB league and the QBs are rated higher due to our 6 point per passing TD setting. In my opinion that represents the NFL better than a standard scoring league since in the NFL good QBs are vital to most successful teams.

Monday, December 8, 2014

SQL Server Calculating Distance using GPS

How far is it from Colorado Springs, CO to the Limon, CO Municipal Airport? If you just blurted out 64.53 miles than you are correct. Why should you know that? I have no clue. I do know however that on several occasions I have had a list of GPS coordinates and have thought to my self, "Self! How do I compute the distance to other points using SQL Server".

Well friends today I finally needed this functionality in some side project I was working on. The goal was to allow a user to input a location, geocode that location to find Latitude and Longitude, and then look up all the other locations in the database within 250 miles of the entered location. This is actual distance, not driving distance.

I used the Google Geocode API to do all the geocoding heavy lifting. Once I had my source GPS coordinates I searched around and found a SQL Script to take in GPS coordinates and return the distance in  meters. Added some American math to get miles and boom, my work here is finished.

I had already completed a set of Python scripts to load NOAA weather data on a daily basis. Therefore the maps hack was just an extension of the already completed website.

I added a link on the Info Window for each pin that allows you to snag the weather data for the NOAA weather station you select. You just click the Get Weather Link

You are taken back to the NOAA weather data page with past 30 days of weather data for the selected NOAA weather station.

I hope to use this functionality in some of my other current and future projects. 

Here is a video demo of the calculating the distance using SQL Server.

Monday, December 1, 2014

Energy Usage

It's time for the annual "How much power does the Christmas tree consume" game. I know you were all chomping at the bit, frantically hitting F5 on the blog here in anticipation. I typically use my Energy Calculator Google Sheet to perform the calculation, however this year I thought it would be fun to throw together a quick web site energy usage calculator (optimized for mobile of course) using the Foundation Framework we all know and have grown to love.

Without further ado, here are this year's numbers:


  • We have a total of 12 x 100 light strands for a total of 1200 lights.
  • The lights are separated into 3, 4 strand chains (kept blowing strands last year)
  • The tree is pulling a total of 225 watts
  • Total cost for 1 hour of tree operation $0.02475
  • Rough projection of tree usage: 125 hours (4 hours/day for 25 days + some fudge)
  • Grand total of $3.03 {high hat crash psshshshshshs}

There you go folks: $3.03 total cost. If you want to see how much power your various appliances are using, I recommend you pick up a Kill-A-Watt. These are great stocking stuffers for the power conscious and/or stats lover on your Christmas list.

You can use the Energy Usage site to plug in your values here:

Powershell Windows Mount Point Free Space

I needed a quick script to monitor the free space on a cluster server using Windows Mount points. Here is the script: