ReadMe File for Bid Tracking Software: WCSTrack.xls v4/14/97 Summary. WCSTrack.xls is software for tracking the progress of the Federal Communications Commission's Wireless Communication Services Auction, Auction No. 14. With this bid tracking tool, bidders can import round results and analyze the bidding data. The software is a Microsoft Excel v5 WorkBook, which runs on Microsoft Windows v3.1 or later. Disclaimer. The Commission makes no warranty whatsoever with respect to the auctions tracking software packages. In no event shall the Commission, or any of its officers, employees, or agents, be liable for any damages whatsoever (including, but not limited to, loss of business profits, business interruption, loss of business information, or any other loss) arising out of or relating to the existence, furnishing, functioning or use of the auctions tracking software packages that are available to the public in connection with the Commission's spectrum auctions. Moreover, no obligation or liability will arise out of the Commission's technical, programming or other advice or service provided in connection with the auctions tracking software packages. Software and Hardware Requirements. At a minimum, you will need the following: Microsoft Windows v3.1 or later Microsoft Excel v5 or later 16MB of available hard disk space (a pentium processor is recommended) 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 user manuals. Installation. 0. Make sure you have 16MB of available space on your hard disk. 1. Download the file README14.TXT (an ascii-dos text file) and CU_TRACK.EXE to your c:drive. [You can install WCSTrack on a different drive, but you will have to change a constant in WCSTrack.xls after installation. This is not recommended.] For illustrative purposes,this example assumes that you download the files to the root directory c:\. 2. At the DOS prompt (i.e. c:\>) type:WCSTrack This will create the required subdirectories and uncompress the necessary files. These subdirectories contain the auction data as described at the end of this file. [If you get an error message after running WCSTrack.exe, it is likely that the file was corrupted in downloading. Download the files again and retry.] The tracking tool is contained in the Excel v5 WorkBook,WCSTrack.xls and a backup is in WCSTrack.bak. 4. To begin using the tool, start Windows, load Excel, and open the file WCSTrack.xls. This file is in the directory c:\fcc\wcs\ . 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 WCSTrack.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 WCSTrack.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 10, but you did not yet download the round results from round 10 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 WCSTrack.xls was damaged in some way. Close WCSTrack.xls, and then use the Windows FileManager to copy it to WCSTrack.old and then copy the backup version WCSTrack.bak to WCSTrack.xls. Open WCSTrack.xls (which should be the original, unmodified version) and attempt to duplicate the problem. Technical assistance can be obtained from the FCC Tech SupportHotline at (202) 414-1250. Description of the Workbook.WCSTrack.xls consists of several worksheets, summarizing the auction data, and several modules written in Visual Basic for Applications, containing the source code. Each is described below. Worksheets with Static 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. BidderData. Contains the basic bidder data. 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? 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 the net bid that was withdrawn. 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 licenses are in each Phase. Modules in WCSTrack.xls (This is the source code for the WCSTrack.xls tool. It should not be modified.) ModConst. Global constants available to all modules. If you installed WCSTrack.xls in a directory other than c:\fcc\wcs, you will need to change the AuctionDir constant to reflect 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 WCSTrack.xls, you will notice a new menu item, Auction14, 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 1 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 tohttp://www.fcc.gov/wtb/auctions/wcs14/wcs1.html] The round results are available for ftp approximately 10 minutes after the close of the bidsubmission period. At this point, the four required data files are posted. These data can beimported following the steps below. 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 cd c:\fcc\wcs\temp Change local directory to \temp cd /pub/Auctions/WCS/Auction_14/Results Change to auction data location binary You are getting binary files mget 14_001?.dbf You want all dbf files for rnd 1 bye This ends your ftp session 2. Change to the temporary subdirectory, c:\fcc\wcs\temp, and type file This runs the batch program file.bat, which places the round results (stored in the c:\fcc\wcs\temp subdirectory) into the appropriate subdirectories. 3. Switch back to Excel and with WCSTrack.xls open select Import All from the Auction14 menu. Type 1 when asked which round to import. It may take up to 30 seconds to import around on a 90 MHz Pentium computer. 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 Auction14 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 Auction14 menu. Data can be selected by market or by bidder. For example, if you want to see all the bids for the entire auction on licenses me01a and re02c, you would select Analysis then Market from the Auction14 menu. You would then select "Market Search". You then select the rounds for which rounds you want to select data. Type 1-3 for rounds 1 to 3. Then you are asked to select the markets you wish to search, Type me01a, re02c. You must separate entries by commas, and include the specific block with the market name. Finally, you must choose the type of data you would like to displayt. Click on All Bids to select all the bids placed on those licenses in rounds 1 through 3. The data is then retrieved and placed in a workbook named Market.xls. If this file already exists, you will be asked if you wish to replace it. Sheet2 of Market.xls is a PivotTable (see the Excel User's Guide), that displays all the bids for the specified markets and rounds. Each row in the table is a round in which a bid was placed in one of the markets. There are several things to note about the Analysis procedure: The display of the results in Pivot Table format is limited to 256 columns, if your query exceeds this limitation, you will be prompted by software and directed to the raw data for analysis ("sheet 1" of either market.xls or bidder.xls). When selecting markets, you must use commas between entries. Example, re01d, me04b. You can select by market number 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 then Bidder from the Auction14 menu. For example, you might want to know what markets bidder 1234 has bid on in the first 10 rounds. Specify rounds 1-10, bidder 1234, and All Bids. A PivotTable is created in the workbook, Bidder.xls. 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. Details of the calculations and labels for each data item have been included on the data sheets as notes. See your Excel manual regarding viewing notes within cells. Directory Structure. Proper installation creates several subdirectories, where the basic auction data are stored. All are subdirectories of the auction directory (c:\fcc\wcs 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 14_001s.dbf All submitted bids for round 1 \dbfHigh 14_001h.dbf High bids before withdrawal \dbfWdraw 14_001w.dbf Bid withdrawals in round 1 \dbfElig 14_001e.dbf Eligibility after round 1 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 bids1.dat All bids in round 1 \ExcelDat\Bidders bid1.dat Bidder data \ExcelDat\Markets mark1.dat Market data \ExcelDat\Withdraw with1.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.)