ReadMe File for Bid Tracking Software: 800Track.xls v10/21/97 I. Summary. 800Track.xls is software used to track the progress of the Federal Communications Commission's 800 MHz SMR, Upper 200 Channels Auction, Auction ("800 SMR"), Auction No. 16. With this bid tracking tool, users can import round results and analyze the bidding data. The tracking tool is integrated and runs with 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.**** II. 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 Personal Computer with a 486 processor (a pentium processor is recommended) III. User Requirements. Please note: 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 Thorough understanding of 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, nor the FCC auctions. You are urged to consult the appropriate user manuals.) IV. Installation. 1. Ensure there is 16MB of available space on your hard disk. 2. Download the file 800TRACK.EXE to your c:drive. [You can install 800Track on a different drive, but you will have to change a constant in 800Track.xls after installation. This is not recommended.] For illustrative purposes, this example assumes that you download the files to the root directory c:\. 3. At the DOS prompt (i.e. c:\>) type:800Track.exe 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 800Track.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, 800Track.xls and a backup is in 800Track.bak. 4. To begin using the tool, start Windows, load Excel v5 or later, and open the file 800Track.xls. This file is in the directory c:\fcc\smr800\ . V. Customizing the Workbook. The 800Track.xls can be modified to meet your specific requirements. However, think carefully before modifying the workbook. Any changes you attempt may inadvertently damage the existing features of the workbook. Additionally, if a new version of the software is released, your changes would be lost if you switch to the new version. The best way to introduce your modifications is to create another workbook that you have linked to the desired data in 800Track.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 800Track.xls. In defining the links, you should use the named ranges. VI. Trouble Shooting. If you encounter errors with the tracking tool, try to isolate the problem first and try to duplicate it. If the problem persists, it may be that 800Track.xls was damaged in some way. Close 800Track.xls, and then use the Windows File Manager to copy it to 800Track.old . Copy the backup version 800Track.bak to 800Track.xls. Open 800Track.xls (which should be the original, unmodified version) and attempt to duplicate the problem. If this does not solve the problem, feel free to contact the FCC tech Support Hotline at (202) 414-1250 for assistance. Please note: 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 a specific round, but you did not yet download the round results from that round 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 re-import. VII. Description of the Workbook. 800Track.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. 1. Worksheets with Static Auction Data: A. MarketData. Contains the basic market data. The data in this sheet are set before the auction begins and do not change during the auction. B. BidderData. Contains the basic bidder data. Please note: The MarketData and the BidderData sheets are protected when the Tracking tool is installed. To perform any sorts, select "Protect" from the "Tool" menu and select "Unprotect". We recommend that these pages remain locked to protect your data from being inadvertently changed. 2. Worksheets Based on the Latest Round Results: A. 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. Please note that some of the data may be expanded beyond the print range. B. Markets. Presents data for each license. C. Bidders. Displays data for each bidder. Provides eligibility, current activity, and other measures. D. Rounds. Displays summary measures for each round. Activity, revenue, price, and eligibility measures are listed. E. Withdrawals. Lists all withdrawals up to the current round and the net bid withdrawn. F. Rules. This gives the current stage, the stage transitions, and the required activity levels in each stage. G. UserSort. Stores the user defined sort orders. H. SubMarkets. A table which specifies which licenses are in each Phase. I. Modules in 800Track.xls (This is the source code for the 800Track.xls tool. It should not be modified.) J. ModConst. Global constants available to all modules. Please note that if you install 800Track.xls in a directory other than c:\fcc\smr800, you will need to change the AuctionDirconstant to reflect your installation directory. K. ModImport. Contains the procedures used to import the round results. L. ModSelect. Contains the procedures used to select market or bidder data for analysis. M. ModSorts. Contains the procedures used to sort the worksheets in the desired order. N. ModTesting. A temporary module used for testing. VIII. Auction Menu and Analysis. 1. After opening 800Track.xls, you will notice a new menu item, Auction16, in the Excel Menu Bar. Select this item to access the auction procedures: to import round results, analyze the data, and/or define user sorts. 2. Importing Round Results. The steps used to import the results for round 1 and all subsequent rounds are provided 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/wtb/auctions/smr200u2.html and click on the "Data (Round Results)"] which are available for ftp within approximately 10 minutes after the close of the bid submission period. At this point, the four required data files are posted. These data can be imported following the steps below. A. 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 Self selected cd c:\fcc\smr800\temp Change local directory to \temp cd /pub/Auctions/SMR/800/Auction_16/Results/ Change to auction data location binary To Receive binary files mget 16_001?.dbf You want all dbf files for rnd 1 (or use appropriate round number.) bye This ends your ftp session B. Change to the temporary subdirectory, c:\fcc\smr800\temp, and type file. This runs the batch program file.bat, which places the round results (stored in the c:\fcc\smr800\temp subdirectory) into the appropriate subdirectories. C. Return to Excel and with 800Track.xls open select Import from the Auction16 menu. Type 1 (or appropriate round number) when asked which round to import. It may take up to 80 seconds to import the data 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. Please ensure that the files are in place. You may have a requirement for data in an earlier round, if so, you may re-import the data from that round. 3. Sorting Tables. The data in the sheets, MarketData, BidderData, Markets, and Bidders, may be sorted. 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 Auction16 menu. After an import, the data is automatically sorted according the User Defined sort for each sheet. 4. Analyzing Auction Data. A. A main feature of the bid tracking tool is the ability to analyze auction data in many different ways. To do this you may select Analysis from the Auction16 menu. Please note there are several constraints when using the Analysis feature of the 800Track.xls: Data may be selected by market or by bidder. For example, if you want to see all the bids for the entire auction on licenses E001-A and E002-C, 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 E001-A, E002-C. 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 display. 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 Pivot Table (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. B. 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). C. When selecting markets, you must use commas between entries. Example, E001-A, E004-B. D. You can select by market number or Last Activity. Last Activity selects all markets that were active during the rounds selected. E. To select data by bidder, select Analysis then Bidder from the Auction16 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 Pivot Table is created in the workbook, Bidder.xls. You may also 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 Pivot Tables, we recommend you read the section dealing with Pivot Tables in the Excel User's Guide. F. 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. G. If you experience an error message "Can't find project or library files" upon attempting to run the "Analysis" tools. You may not have all the required files or library references available to Excel. A "Reference" window will appear. Uncheck all the files that have "Missing" preceeding the Reference and save the Tracking Tool and rerun the Analysis. IX. Directory Structure. Proper installation creates several subdirectories, where basic auction data are stored. All are subdirectories of the auction directory (c:\fcc\smr800 in the default installation). Four 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 16_001s.dbf All submitted bids for round 1 \dbfHigh 16_001h.dbf High bids before withdrawal \dbfWdraw 16_001w.dbf Bid withdrawals in round 1 \dbfElig 16_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.)