Excel VBA: Generate Macros for Web Test Automation

This application illustrates a unique way of utilizing Microsoft Excel VB Macros to generate and execute iOpus batch files (.iim). The purpose is to automate the CBS.Sportsline Fantasy Football Free-Agent Bidding System using Excel VB Macros and iOpus iMacros.

To give a little background, fantasy sports (football, baseball, hockey, basketball . even golf) are one of the most popular on-line games in terms of total number of participants. Hundreds of thousands of people participate in fantasy sports on-line through Yahoo!, CBS.SportsLine, ESPN, TSN, etc. In fantasy football, for example, owners select specific individual players (QBs, RBs, WRs, etc.) from around the NFL. As the players on your team perform (TDs, yards gained, etc.), you get points. If your players (Brett Favre, Randy Moss) get more points than the players belonging to your friends (Marshall Faulk, Vinnie Testeverde), you win. If Kurt Warner has a good day (350 yards, 4 TDs), you have a good day. If Jerry Rice has a bad day (0 catches), you have a bad day. Ce la vie. As your individual players perform, so do you.

Most fantasy football leagues hold a draft before the start of the NFL season to determine who gets which players. Some hard-core fantasy football fanatics, however, hold an auction where each fantasy team has a salary cap to maintain while they bid to see who gets who. It's all play money but now you really feel like a GM. CBS.SportsLine supports this somewhat by allowing the Commissioner of a league to designate a wildcard field to represent each player's salary. The Commissioner is responsible for manually maintaining each player's salary. Great. CBS. Sportsline supports a salary cap and player's salaries but what if you wanted to do something really different like extending the auction throughout the year by allowing teams to bid for free-agents each week? Unfortunately, the only way your league could do this would be for every league member to "reconvene" in person each week to have a mini-free -agent auction and have the Commissioner manually enter in the results just like he did for the real auction held just before the season.

Could there be a way to implement an automated Free-Agent Bidding System using CBS.SportsLine? Yes! Combining Microsoft Excel's ability to extract and crunch information from the internet and iOpus's ability to use that information to intelligently interact with the internet will now allow your fantasy football league to do just about anything you can imagine.

CBS.SportsLine allows you to configure your league so that all Adds and Drops must be approved by the commissioner ("Front Office" . "League Settings" . Permissions").

When setting this for your league a funny little thing happens . on the page where owners submit their Adds and Drops, there's a text box which appears called "Note to Commissioner". This is what allows this whole application possible. When claiming players, simply enter in the bid amount in the "Note to Commissioner" text box.

Now what happens? Well, as owners from around your league enter in bids for the next Shaun Alexander, the League Commissioner's Transactions-Requests page (found in Front Office) begins to fill up with requests that the Commissioner must evaluate and respond to:

This sample page seems manageable from a manual perspective but keep in mind that this page would normally contain dozens and dozens of bids . sometimes close to 200! This bidding logic is pretty much the same as eBay in that the highest bidder gets the player at $1 more than the 2nd highest bid. The twist here is that owners can specify "conditional" bids. Notice how in the example above, some of the "dropped" players are the same. These are "conditional" bids. In other words, once a bid is accepted and the player is dropped, the other bids dropping that same player cannot be accepted because the player is no longer on that team's roster. Wouldn't it be nice if you could do this on eBay? You could bid $100 for an antique lamp and then bid $50 for another antique lamp but only if you don't get your first choice.

Anyway, an Excel VB Macro can easily extract these bids and sort them . crunch them . to determine which bids to deny and which bids to accept and for how much. But we're only half-way there as far as processing these free-agent bids. Now that we know who gets who and for how much, how do we actually make the changes? Does the commissioner have to click on accept or deny for each of these bids? What if he makes just one mistake? Catastrophe. For the accepted bids (sometimes there are more than 30), is the Commissioner expected to manually enter in the new salary for each player's wildcard field? For auditing purposes, how can we update the web-site to show everyone how each bid was considered (ex; this bid was exceeded by bid 20208387)?

The answer is iOpus iMacros. iOpus allows you to record and replay your internet activities in the form of iMacros. You can even schedule these activities to be run in batch mode when you're away from your computer.

Back to our free-agent bidding process, how do we use iOpus to process our bids, update our player salaries and post reports? The answer is to have the Excel VB Macro generate the iOpus Internet Macro scripts as .IIM files and then execute them. We can use the Windows Task Scheduler to kick off the FAB (free-agent bidding) Excel VB Macro. FAB will process all pending bids determining who gets who and for how much while, at the same time, generating the .IIM code necessary for the actual processing of the requests (accept/deny), updating of player salaries, and, finally, the posting of the FAB report. The Commissioner does absolutely nothing. Everyone in your fantasy football league wakes up the next morning and . viola .

Now that we described in detail the FAB concept, how does the FAB program actually know which Accept/Deny radio buttons go with each bid? Well, basically, the first thing FAB does is downloads the Free-Agent Bidding Requests web-page. Here, FAB must loop through all of the OLE-Objects on the page because we know that each bid request will always be accompanied by three buttons "Accept", "Deny", and "Nothing". To make things easier, the FAB program simply looks for the "Deny" buttons. Because these OLE-Objects are now part of VB Excel (because we opened the web-page using Excel), each "Deny" button is physically anchored to a cell. We know that the bidding information (which person made the bid, who is the player being bid for, how much is the bid for, who is going to get dropped if the bid is accepted, etc.) is contained in the same row (or, at least, adjacent rows) that the "Deny" button is anchored to. The final important thing here is to remember that each OLE-Object on this web-page has a unique tag called an HTML-Name. FAB needs to remember which bids each of these tags belongs to. This way, FAB can tell iOpus exactly which buttons to press by using the HTML-Name tags.

Now that we're discussing the "Nitty-Gritty", I suppose it would be appropriate to see some of the code. First off, for those of you who are unfamiliar with using excel to get information from the internet, it's as simple as just specifying the URL instead of a file-spec when opening the file. Here's the open statement for the Transactions-Requests:

Ok, pretty cool, FAB has opened the page in Excel. Now things start to get even cooler as we step through the OLE-Objects and gather all of the bids:

' consolidate web-requests into easy to manage "work" table 
i = 0
For Each obj In Worksheets(fabRequests).OLEObjects ' Loop thru all OLE Object in this worksheet 
If Len(obj.Object.HTMLName) = 10 Then ' Accept/Drop radio boxes have HTML NAME length of 10 ...
If obj.Object.Value = "deny" Then ' ... and the "deny" button is on the bottom
           
i = i + 1
           
          TLCol = obj.TopLeftCell.Column
TLRow = obj.TopLeftCell.Row
           
Range("Team").Offset(i) = Worksheets(fabRequests).Range(Chr(TLCol + 60) & TLRow - 2)
Range("Add").Offset(i) = Worksheets(fabRequests).Range(Chr(TLCol + 61) & TLRow - 2)
Range("Drop").Offset(i) = Worksheets(fabRequests).Range(Chr(TLCol + 62) & TLRow - 2)
If Range("Drop").Offset(i) = "*" Then Range("Drop").Offset(i) = ""
           
Range("AddID").Offset(i) = Worksheets(fabRequests).Range(Chr(TLCol + 61) & TLRow - 2).Hyperlinks.Item(1).Address
Range("AddID").Offset(i) = Mid(Range("AddID").Offset(i), InStr(Range("AddID").Offset(i), "key="))
Range("AddID").Offset(i) = Mid(Range("AddID").Offset(i), 5, InStr(Range("AddID").Offset(i), "&") - 5)
           
Range("DropID").Offset(i) = Worksheets(fabRequests).Range(Chr(TLCol + 62) & TLRow - 2).Hyperlinks.Item(1).Address
Range("DropID").Offset(i) = Mid(Range("DropID").Offset(i), InStr(Range("DropID").Offset(i), "key="))
Range("DropID").Offset(i) = Mid(Range("DropID").Offset(i), 5, InStr(Range("DropID").Offset(i), "&") - 5)
           
ReqDate = Mid(Worksheets(fabRequests).Range(Chr(TLCol + 60) & TLRow - 1), 13, 99)
Range("BidTime").Offset(i) = Left(ReqDate, Len(ReqDate) - 3)
           
Range("NTC").Offset(i) = Worksheets(fabRequests).Range(Chr(TLCol + 60) & TLRow - 0)
Range("Bid").Offset(i) = OnlyN(Worksheets(fabRequests).Range(Chr(TLCol + 60) & TLRow - 0))
Range("Points").Offset(i) = "=VLOOKUP(RC[-1],'Standings'!R1C1:R14C2, 2,FALSE)"
           
If Range("AddID").Offset(i) <> "" _
And Range("Bid").Offset(i) < 5 Then
          Range("Action").Offset(i) = "deny"
Range("Comments").Offset(i) = "Less than minimum"
Range("Comments").Offset(i).Font.Color = vbRed
End If
           
Range("BidID").Offset(i) = obj.Object.HTMLName
           
If Range("AddID").Offset(i) = "" Then
Range("Add").Offset(i) = Range("BidID").Offset(i)
Range("Bid").Offset(i) = 9999
End If
           
End If
End If
Next
  
bids = i ' Record the number of bids

Notice how we're using the location of the "Deny" button as the anchor surrounding the actual bid. We know that various entities are simply offsets to the cell in which the "deny" button is found (added player is found 2 rows up and 3 columns back, etc.). We also know that obj.Object.HTMLName is the unique Bid ID which iOpus will need later when forming the <TAG> line for the "Accept/Deny" radio button.

Now that we've gathered all of the bids and, most importantly, their respective HTML Tags, the FAB program goes through the process of sorting and crunching the bids to determine who gets who and for how much. One of the things FAB does is to deny all bids whose dollar amounts were text-only or less than the minimum $5. Then, FAB sorts by chronologically by added player to start evaluating whose bid is the highest for each player. For each accepted bid, FAB loops though the bid list denying all lower bids for the same player and denies all "conditional" bids from the same owner (the drop player is the same as the one for the accepted bid). Blah, blah, blah. If you want to see the code for that stuff, you'll find the VB code, in its entirety, at the bottom of this document.

Now, FAB is ready to start generating the iOpus Internet Macro script file or IIM file. We know which web-page contains the requests, we know the TAG reference for each radio button, and we know whether to accept or deny each button. Here's the code which generates the IIM script:

' Produce .IMM file
Worksheets.Add
ActiveSheet.Name = "IIM " & NFLWeek
Cells.Font.Size = 8
Range("A:A").ColumnWidth = 255
Range("A:A").NumberFormat = "@"

' Start with the URL
Range("A" & ActiveSheet.UsedRange.Rows.Count) = _
"URL GOTO=http://football2126.fantasy.sportsline.com/mp/frontoffice/transaction-requests?league=tjltjl&owner=12771&"

' Loop through the accepted or denied bids
For i = 1 To bids
Range("A" & ActiveSheet.UsedRange.Rows.Count + 1) = "TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:" & _
Range("BidID").Offset(i) & "&&VALUE:" & Range("Action").Offset(i) & " CONTENT=" & Range("Action").Offset(i)
Next I

' Press <OK> to submit the form
Range("A" & ActiveSheet.UsedRange.Rows.Count + 1) = _
"TAG POS=1 TYPE=INPUT:IMAGE FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:okay&&VALUE:"

URL GOTO=http://football2126.fantasy.sportsline.com/mp/frontoffice/transaction-requests?league=tjltjl&owner=12771&
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029403599&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029429940&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029429989&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443286&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443321&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443404&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443432&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443462&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443517&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029443720&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029460856&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029460887&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029460923&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029460949&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029461400&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029506377&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029506473&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029506508&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509228&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509250&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509280&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509300&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509348&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509366&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509381&&VALUE:deny CONTENT=deny
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509401&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:RADIO FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:1029509422&&VALUE:accept CONTENT=accept
TAG POS=1 TYPE=INPUT:IMAGE FORM=ACTION:/mp/frontoffice/transaction-requests ATTR=NAME:okay&&VALUE:
URL GOTO=http://football2126.fantasy.sportsline.com/mp/frontoffice/edit-league-permissions?league=tjltjl&owner=12771&random=2315&

There's more code which generates the IIM lines necessary for modifying the player's salaries. Finally, for auditing purposes, people are going to want to know from time to time why their bids weren't accepted. Because of this, FAB gathers comments for each and every request indicating why is accepted or denied the particular bid. If a bid was denied, FAB makes a comment which includes the Bid ID of the bid that caused this bid to get denied. FAB generates a few extra lines of IIM code to add all of those comment as one tablular league news item. Again, if you're interested in that type of coding detail, refer to the FAB VB code found, in its entirety, at the end of this document.

Now that we've generated all of the IIM code we could dream of the simple matter of saving it as a text file in the IIM Macros directory and then invoking the IIM macro to do its thing! Here's the code for that: