ReadMe File for Bid Tracking Software: MTATrack.xls, v1/18/96 ************************************************************* SUMMARY. MTATrack.xls is software for tracking the progress of the Federal Communications Commission's spectrum auctions. With this bid tracking tool, bidders can import round results and analyze the bidding in the MTA Broadband PCS Auction. The software is a Microsoft (R) Excel (TM) v5 WorkBook, which runs on Microsoft Windows (TM) v3.1 or later. This is a test version designed to demonstrate the tracking tool's features. The use of the MTA Broadband PCS auction data allows the user to compare results with the MTA auction. DISCLAIMER. This is unsupported software. It has not undergone the extensive testing common of commercial software. Testing of this software is the responsibility of the user and not the FCC. It is distributed without any warranties. The FCC is not liable for consequential damages. Use this software at your own risk and only after your own testing. SOFTWARE and HARDWARE REQUIREMENTS. You will need at a minimum the following: * Microsoft Windows v3.1 or later * Microsoft Excel v5 or later * a computer able to run the above software * 8MB of available hard disk space USER REQUIREMENTS. This software is not designed for novice computer users. To be used effectively, you must have extensive experience with: * Microsoft Windows v3.1 or later * Microsoft Excel v5 or later * the FCC simultaneous multiple round auction format This document assumes that you have such experience. It does not attempt to educate you about Windows, Excel, or the FCC auctions. You are urged to consult the appropriate manuals elsewhere. INSTALLATION. 0. Make sure you have 8MB of available space on your hard disk. 1. Download the files SetupMTA.exe, MTAData.zip, and MTATrack.zip to your C: drive. [You can install MTATrack on a different drive, but you will have to change a constant in MTATrack.xls after installation. This is not recommended.] 2. At the DOS prompt type: setupmta This will create the installation file Install4.bat. 3. At the DOS prompt type: install4 This will create the required subdirectories and uncompress MTATrack.zip. These subdirectories contain the auction data as described at the end of this file. [If you get an error message when running Install4.bat, it is likely that a file was corrupted in downloading. Download SetupMTA.exe, MTAData.zip and MTATrack.zip again and try again.] The tracking tool is contained in the Excel v5 WorkBook, MTATrack.xls and a backup is in MTATrack.bak. 4. To begin using the tool, start Windows, load Excel, and open the file MTATrack.xls. This file is in the directory c:\fcc\broad\mta. CUSTOMIZING THE WORKBOOK. Once you gain experience using the tracking tool, you will see ways to change or extend the workbook for your particular needs. You should think carefully before modifying the workbook. First, your changes may inadvertently damage the existing features of the workbook. Second, when a new version of the software is released, your changes would be lost if you switch to the new version. Hence, the best way to introduce your extensions is to create another workbook that is linked to the desired data in MTATrack.xls. Both workbooks can be open at the same time in Excel and the links can be updated automatically (see the Excel user manual). Most data items are named ranges in MTATrack.xls. In defining the links, you should use the named ranges. TROUBLE SHOOTING. If you are having a problem with the tracking tool, first try to isolate the problem. The most common problem is that a required data file is missing (you will get a "File not found." run-time error message). For example, you tried to import data for round 100, but you did not yet download the round results from round 100 or failed to place them in the required subdirectories. To solve the problem, download the results, place them in the right subdirectories (see below), and retry the import. Try to duplicate the problem. If the problem persists, it may be that MTATrack.xls was damaged in some way. Close MTATrack.xls, and then use the Windows FileManager to copy it to MTATrack.old and then copy the backup version MTATrack.bak to MTATrack.xls. Open MTATrack.xls (which should be the original, unmodified version) and attempt to duplicate the problem. DESCRIPTION OF THE WORKBOOK. MTATrack.xls consists of several worksheets, summarizing the auction data, and several modules written in Visual Basic for Applications (TM), containing the source code. Each is described below. WORKSHEETS WITH PERMANENT AUCTION DATA. * MarketData. Contains the basic market data. The data in this sheet are set before the auction begins and do not change during the auction. Since this workbook was designed for the C-block PCS auction, the relevant markets are the 493 BTAs. However, since this workbook is setup to use the data from the MTA PCS Auction as test data, the 99 MTA licenses are listed as the first 99 BTA licenses (numbered BTA 1 to BTA 99). The BTA names in these 99 markets have been replaced with the MTA name and block letter (e.g., ChicagoB). Note that the BTA numbers in this test workbook do not have any significance. Of course, in the actual workbook for the C-block auction, the BTA numbers will be the actual market numbers as specified in the Bidder Information Package. * BidderData. Contains the basic bidder data. An S in column Pref indicates that the bidder is eligible for a small business credit. Since no firm gets a credit in the MTA auction, none of the bidders are designated with an S. WORKSHEETS BASED ON THE LATEST ROUND RESULTS. * Summary. Contains a summary of the results for the current round. The summary includes measures of revenue, bidding activity, prices, and eligibility. The sheet highlights essential information from the round. What markets are most active? Are bidders using jump bids or raising their own bids? What markets have the highest (lowest) prices? Were there any bid withdrawals? Were there any changes in eligibility? Who has to bid in the next round to avoid a drop in eligibility? * Markets. Presents data for each license. How active is the license? Who was (is) the high bidder? Is there an outstanding withdrawal? * Bidders. Displays data for each bidder. Eligibility, current activity, and other measures are given. * Rounds. Shows several summary measures for each round of the auction. Activity, revenue, price, and eligibility measures are listed for each round. * Withdrawals. Lists all withdrawals up to the current round and any outstanding withdrawal penalties. Penalties are calculated on a net bid basis. That is, the current penalty is the withdrawn bid, net of the bidding credit of the withdrawing bidder, minus the high bid, net of the bidding credit of the high bidder, or zero, whichever is greater. * Rules. This gives the current stage, the stage transitions, and the required activity levels in each stage. * UserSort. Stores the user defined sort orders. * SubMarkets. A table which specifies which BTAs are in each MTA. This table has been adapted to the MTA test data. MODULES IN MTATrack.XLS. This is the source code for MTATrack. It should not be modified. * ModConst. Global constants available to all modules. If you installed MTATrack in a directory other than c:\fcc\broad\mta, you will need to change the AuctionDir constant to your installation directory. * ModImport. Contains the procedures used to import the round results. * ModSelect. Contains the procedures used to select market or bidder data for analysis. * ModSorts. Contains the procedures used to sort the worksheets in the desired order. * ModTesting. A temporary module used for testing. AUCTION MENU. After opening MTATrack.xls, you will notice a new menu item, Auction4, in the Excel Menu Bar. This menu item (Alt-A to select it) is used to access the auction procedures: to import round results, analyze the data, or define user sorts. For example, to import a round, you would type Alt-A I. The main procedures are described below. IMPORTING ROUND RESULTS. The steps used to import the results for round 10 in the MDS auction are given below. These steps assume that you download the data files over the Internet using FTP. [Alternatively, you can download the files using a Web browser. Connect to http://www.fcc.gov and then click on Auctions.] Since this is a test version, the test data have already been installed on your hard disk; hence steps 1 and 2 should be skipped. Steps 1 and 2 are included only to show you what the process would be in a real auction. Indeed, steps 1 and 2 MUST be skipped, since the included data files for the MTA auction have been translated to the new FCC auction format -- the MTA files at fcc.gov are in the original format, which is incompatible with the new format. To practice downloading and importing round results, you should use MDSTrack. The preliminary (pre-withdrawal) round results are available for ftp approximate 30 minutes after the close of the bid submission period. At this point, the first three data files (S, X, and E) are posted. These data can be imported following the steps below. The net and gross revenue calculations based on pre-withdrawal data do not include the current bid withdrawal penalties. The final round results are available for ftp approximately 30 minutes after the close of the withdrawal period. At this point, the remaining three data files (Y, M, and W) are posted. Simply reimport the round to update the preliminary results. 1. In a DOS session, type the following commands: COMMAND COMMENT ftp fcc.gov Begins ftp session with fcc.gov anonymous Your user name is anonymous password Any password will do lcd c:\fcc\mds\temp Change local directory to \temp cd pub/Auctions/MDS/BTA/Results Change to auction data directory binary You are getting binary files mget 6_010?.dbf You want all dbf files for round 10 bye This ends your ftp session 2. Change to the auction directory, c:\fcc\mds\temp, and type file. This runs the batch program file.bat, which places the round results (stored in the c:\fcc\mds\temp subdirectory) into the appropriate subdirectories. 3. Switch back to Excel and with MTATrack.xls open select Import All from the Auction4 menu. Type 10 when asked which round to import. It takes about half a minute to import a round on a 90 MHz Pentium computer when there are 30 bidders and 99 licenses. The import time with 100 bidders and 493 licenses may be well over 1 minute. Please be patient. If you get an error message saying that a file is not found, it is probably because the dbf files are not in the appropriate subdirectories. Make sure that the required files are in place. If you want to see the data from an earlier round, simply reimport the desired round. SORTING TABLES. The data in the sheets, MarketData, BidderData, Markets, and Bidders, can be sorted any way you like. Simply click on the Sort button and then select the desired sort. You can change the User Defined sort by selecting User Sort in the Auction4 menu. After an import, the data is automatically sorted according the User Defined sort for each sheet. ANALYZING AUCTION DATA. A main feature of the bid tracking tool is the ability to analyze the auction data in many different ways. Often you will want to look at data from many rounds at once. This is accomplished by selecting Analysis from the Auction4 menu. Data can be selected by market or by bidder. For example, suppose that you want to see all the bids for the entire auction in MTAs 1 to 3. Then you would select Analysis Market from the Auction4 menu. It asks you how you want to select the markets. Click on MTA to select by MTAs. Then it asks you for which rounds you want to select data. Type 1-111 for rounds 1 to 111 (the last round of bidding in the MTA auction). Then it asks you which markets you would like to select. Type 1-3 to select MTAs 1 to 3. Finally, it asks what type of data you would like to select. Click on All Bids to select all the bids in these markets. It then selects the specified data and puts it in a workbook, Market.xls. If this file already exists, it asks if you would like to replace it. Sheet2 of this workbook is a PivotTable (see the Excel User's Guide), which displays all the bids for the specified markets and rounds. This is handy to see who the competitors are in a market and see the progress of the bids. Each row in the table is a round in which a bid was placed in one of the markets. The MTA markets, Chicago, Los Angeles, and New York, are shown. The markets are listed alphabetically as are the bidders in each market. There are several things to note about the Analysis procedure: * When selecting markets, you can use commas and dashes in any combination. For example, 1-3,4,8,10-12,16 are the markets numbered 1,2,3,4,8,10,11,12,16. * You can select markets by MTA or Last Activity. Last Activity selects all markets that were active during the rounds selected. * Once the PivotTable is created, you can modify it to see the selected data in different ways. For example if you want the data organized by bidder and then by market, simply drag the Bidder button to the left of the Market button in the PivotTable. If you want to save your modified table, select Save As from the File menu to save the file under a different name. * To select data by bidder, select Analysis Bidder from the Auction4 menu. For example, you might want to know what markets bidder 3 has bid on since round 65. Specify rounds 65-111, bidder 3, and All Bids. A PivotTable is created in the workbook, Bidder.xls. You can see that PacTel (bidder 3) only bid in Los Angeles and San Francisco after round 64. * You can change the orientation (for example, have rounds as columns and bidders as rows) of the pivot table by clicking on the Pivot Table button and dragging the data items to the appropriate spots. If you are not familiar with PivotTables, you should spend some time reading the Excel User's Guide. PRINTING REPORTS. You can define and print standard reports by selecting Print Reports in the File menu. The report "Round Results" has been created as a sample. This report is easily customized. Again see the Excel User's Guide. DEFINITIONS. Below are definitions of some of the items that may not be clear from the headings in the worksheets. Many of items in the MTA PCS Auction are best displayed in millions (M). These are the units that are predominantly used in MTATrack.xls. For subsequent auctions, the units typically will be in thousands (k). Chg denotes the change from the prior round in the item. The definitions are organized by worksheet. * Summary - Gross Revenue = total auction revenues, including current withdrawal penalties, ignoring bidding credits for small businesses. - Net Revenue = total auction revenues, including current withdrawal penalties, net of bidding credits for small businesses. Net Revenue equals gross revenue, since there are no bidding credits in this auction. - New Bids = gives the number of new bids in the round and the quantity of new bids (in pops) relative to the total quantity of pops being auctioned. - New High = gives the number of new high bids in the round and the pops covered by the new high bids relative to the total pops up for auction. - Top Ten Most Active Markets = the most active markets in terms of the number of new bids in each market. - Top Ten Bid Increments = the new bids that increased by the largest increments. An increment of 2 means that the raise was equal to 2 minimum bid increments. The smallest acceptable increase is 1 bid increment. - Raising Own Bid = firms that raise their own prior high bid are listed. - Average Price = unweighted is a simple average of price on each license; pop weighted is an average weighted by population. - Eligibility Ratio = total eligibility / total spectrum being auctioned. - Chg in Eligibility = change in eligibility in million pops. - Bidders at Risk of Drop in Eligibility = a list of bidders who will lose eligibility if they do not bid in the next round; required activity is the number of pops in millions on which they must bid to maintain their current eligibility. * Markets - MTA = MTA number. - Num Bids = number of new bids in the round. - P = an S indicates high bidder has a small business preference. - Round = the round in which the current high bid was placed. - Net Bid = the bid net of bidding credit for small business. The net bid equals the gross bid, since there are no bidding credits in this auction. - Price = high bid/pops. - # of Bid Increments = raise in bid from prior round measured in the number of minimum bid increments (e.g. 1 means that the minimum bid was placed). * Bidders - Pop Coverage = New is the total population covered by the bidder's new bids. High is the total population covered by the bidder's high bids. - Activity Measures = High Dollars is the sum of the bidder's high bids. Activity is the sum of the bidder's high bids and active bids (i.e., a bid that was topped by another in the current round). Both are net of any bid credit. Required Activity is the required activity in million pops that the bidder must have in the next round in order not to lose eligibility. DIRECTORY STRUCTURE. The installation program creates several subdirectories, where the basic auction data are stored. All are subdirectories of the auction directory (c:\fcc\broad\mta in the default installation). Six subdirectories of the auction directory contain the round results in dbf format. These data files are downloaded from the FCC. SUBDIRECTORY SAMPLE FILE DESCRIPTION \dbfAll 4_010S.dbf All submitted bids for round 10. \dbfHigh 4_010X.dbf High bids before withdrawal. \dbfHighW 4_010Y.dbf High bids after withdrawal. \dbfMin 4_010M.dbf Minimum accepted bids for round 11. \dbfWdraw 4_010W.dbf Bid withdrawals in round 10. \dbfElig 4_010E.dbf Eligibility after round 10. Four subdirectories of the auction directory contain the round results in a form used by the workbook. These data files are created when you import a round. SUBDIRECTORY SAMPLE FILE DESCRIPTION \ExcelDat\AllBids bids10.dat All bids in round 10. \ExcelDat\Bidders bid10.dat Bidder data. \ExcelDat\Markets mark10.dat Market data. \ExcelDat\Withdraw with10.dat Withdrawal data. Finally, the subdirectory \Temp is used as a temporary location for downloading the dbf files. See step 1 of Importing Round Results.