Excel for Multi-Unit Auction 2 :A Users’ Manual (excerpt)∗∗The original version of this note was written by Shuya Abe (Graduate School ofInformation Science, Osaka University) and Naoki Watanabe (Graduate School of Business
Administration, Keio University).

1

Contents
1 Introduction
1.1 Recommended Operating Environment . . . . . . . . . . . . .
1.2 Properties of the VCG mechanism . . . . . . . . . . . . . . . .
1.3 Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

33442 Structure of the software package
2.1 man sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 result sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5563 Using Excel for Multi-Unit Auction 293.1 Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3.2 Running the program . . . . . . . . . . . . . . . . . . . . . . . 103.3 Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3.4 Optional: Generating random input data . . . . . . . . . . . . 12
4 Troubleshooting
4.1 The Development ribbon is not displayed . . . . . . . . . . .
4.2 An error message appears when the program is run . . . . . .
4.3 No error message appears, but the program cannot computea correct allocation . . . . . . . . . . . . . . . . . . . . . . . .

212121414

1Introduction

This material is a user’s manual for a software Excel for Multi-Unit Auction
2 which runs on a computer to implement the Vickrey-Clarke-Groves (VCG)
mechanism and run the Greedy-Based Algorithm (GBA) for determining theallocation of some units of a commodity and the payments of bidders in
multiple-unit auctions. The GBA is an approximation algorithm developed
by Takahashi and Shigeno (2011) which computes the auction outcomes much
faster than the VCG.1

1.1

Recommended Operating Environment

As of December 2019, the first version of Excel for Multi-Unit Auction 2 isreleased; the filename is vcg-gba.xlsm and may be freely downloaded fromthe following URL.
http://labs.kbs.keio.ac.jp/naoki50lab/vcg-gba.xlsm
The download may be blocked in some corporate or educational environments, because this software uses the macro function of Excel. In such acase, users are asked to download the program via their personal internetenvironments.
The screen-shots in this manual were taken in the environment indicatedin Table 1. Excel for Multi-Unit Auction 2 runs on computers that can use
Excel VBA (Visual Basic for Applications). The recommended environment
is listed in Table 2.
Table 1: Operating environment in which screenshots were captured
OSWindows 10 EnterpriseExcel
Microsoft Oﬃce 2016 Excel
CPUIntel(R) Core(TM) i7-3770K CPU @ 3.50GHz 3.90GHz
Memory
8 GB
Disk
1 TB

1

Takahashi, S., Shigeno, M., 2011. Approximation algorithms for a winner determination problem of single-item multi-unit auctions. JSIAM Letters 3, 29-32.

3

Table 2: Recommended environment
OSExcelCPUMemory
Disk

1.2

Windows 7–，Mac OS X 10.8–
Microsoft Oﬃce 2013–，Microsoft Oﬃce for Mac 2016no requirement if Excel works
at least 2GB
no in particular

Properties of the VCG mechanism

Consider a situation in which multiple units of a homogeneous commodity areauctioned oﬀ to bidders. Upon entering the bids for all units into Excel for
Multi-Unit Auction 2 for the settlement of that competitive bid, the program
computes an allocation of those units and the payments of bidders. Excelfor Multi-Unit Auction 2 has two computational methods. One is known as
the VCG mechanism and the other is its approximation algorithm calledthe Greedy-Based Algorithm (GBA). The computation process in the VCG
mechanism is illustrated with an example in the following note.
http://www.kansai-u.ac.jp/riss/research/publications/public_files/
riss_dp/RISS_DP_No68.pdf
GBA is an approximation algorithm for the VCG mechanism. The explanation is provided in the following note.
https://www.kansai-u.ac.jp/riss/research/publications/public_files/
riss_dp/RISS_DP_No50.pdf

1.3

Notes

(1) Users need to specify initial conditions for the algorithm, as well as valuations and bids for all bidders for all units, by inputting numericalvalues in Arabic Numerals (not double byte characters) without
multiple numbers and without missing values.
(2) Excel for Multi-Unit Auction 2 uses a program written in Visual Basic.
Thus, unlike editing a regular Excel sheet, users cannot restore states bypressing the “backspace” key or by pressing the “Z” key while holding
down the “Ctrl” key (Ctrl-Z). The Excel file is restored to its state prior
to editing by closing the Excel session without saving the file.
4

(3) The computation results are overwritten each time the program is executed. If you wish to save the results of a computation, then copy them
to a diﬀerent file or make separate copies for each Excel file.

2Structure of the software package

Excel for Multi-Unit Auction 2 consists of two Excel spreadsheets (man andresult) and one VBA program. Here we will describe the content of thetwo spreadsheets; the usage of the VBA program is briefly explained at thebeginning of the next section, while for information on reading and writing
VBA code, we ask users to read the standard commercially available referencematerials. When running the software, to facilitate computer calculations,
we assign each bidder a numerical ID to allow bidders to be easily identified
in program input and output.

2.1

man sheet

Each bidder has a certain valuation (the amount that he or she is willing topay) for each possible subset of the k units available, and each bidder entersbids based on these values. The man sheet is used to enter bids in numerical values (not double byte characters) from each bidder for each possible
quantity of the commodity. See Figure 1. The entries in this spreadsheet areinterpreted as follows.
Column A, man id: For the bidder with ID i (man id=i), his or her ID isentered in row 2i of column A in man sheet. The IDs are integers thatstart with 1 and increase consecutively. No bidders have the same ID,
and no ID is omitted. The numbers of bidders and units are set by theuser when configuring initial conditions (Section 3.1), and the bidder
IDs are automatically entered into man sheet accordingly.
Columns B and beyond, unit j: For each bidder in the man sheet, enter in each cell the valuation or the bid for each possible unit of thecommodity. Based on the settings of the initial conditions (Section3.1), the available units will be automatically entered in man sheet as
unit j (j=1, . . . , k). For each bidder and each unit, there is one cellfor the valuation and one cell for the bid. For the bidder with ID i(man id=i), his or her ID appears in row 2i in column A, while valuations are entered in other columns of row 2i. The value in row 2i in thecolumn corresponding to unit j is bidder i’s valuation for j units ofthe commodity. Similarly, the value in row 2i + 1 of the same column
5Figure 1: man sheet
is bidder i’s bid for j units of the commodity. In Figure 1, e.g., for 2
units of the homogeneous commodity (unit 2), the bidder with ID 1(man id=1) has his or her valuation of 120 and enters his or her bid of100. The program requires that values for both the valuation and thebid be entered for all bidders for all units.
randomize button Used to generate random numbers for valuations for thepurposes of conducting simulations.
See, in Figure 1, bids for all 3 bidders for all units (from 1 to 3) have beenentered into the man sheet.

2.2

result sheet

The result sheet displays the units allocated to each bidder, the bidder’svaluation and payment for units allocated to him or her, and the points
he or she earned (valuation minus payment) under the VCG and the GBA,respectively. The various buttons in the spreadsheet are used to execute thecalculations in question (Figure 2).
6

Figure 2: result sheet.

7Column A: (man id): Bidder IDs (in ascending order).
Column B (units (VCG)), Column F (units (GBA)): Units allocated tothe bidder under the VCG and the GBA, respectively.
Column C (val (VCG)), Column G (val (GBA)): Bidder’s valuation for
the units allocated to him or her under the VCG and the GBA, respectively.
Column D (payment (VCG)), Column H (payment (GBA)): Payments ofthe bidder for the units allocated to him or her under the VCG andthe GBA, respectively.
Column E (pts (VCG)), Column I (pts (GBA)): Points (the bidder’s valuation minus his or her payment) the bidder earned under the VCGand GBA, respectively.
Figure 2. According to the valuations and bids shown in Figure 1, thebidder with ID 2 (man id=2) was allocated 4 units under the VCG, for which
his or her valuation was 1032 and the payment was 935, and thus he or she
earned 97 points, whereas under the GBA the bidder was allocated 1 unit,
for which his or her valuation was 11 and the payment was 235, and thus heor she earned -224 points. As is shown in the figure, the GBA generates theresults which are far from those the VCG generates.
Note that the GBA approximates the results computed by the VCG when
all bidders bid their approximately true valuations. The computational procedures of the VCG and GBA are described in the Appendix.
run VCG button: Click on this button to run the calculation of commodity
allocation and payments of bidders according to the VCG mechanism.
run GBA button: Click on this button to run the calculation of commodity
allocation and payments of bidders according to the GBA.
When the program is invoked to perform calculations, if some valuations
are left unspecified in the man sheet, then the calculation may proceed withthose valuations set to 0 in some cases. If some bids are left unspecified, thenthe calculation will terminate prematurely and an error message (Figure 3)
will appear. In such a case, click on the OK buttonin the error message andfill in values for all missing valuations and bids.
As demonstrated in the Appendix, under the VCG mechanism, the sumof all bids for all bidders and for all units allocated to bidders is maximized;
however, there may be more than one such optimal allocation. In such cases,
8Figure 3: Error message presented in the event of one or more bid values
being left unspecified
the program selects an allocation scheme that allocates fewer units to bidders
with lower values of IDs. For example, for a case with 3 bidders and 3 units,
if there are multiple possible allocation schemes that yield equal values of
the total bid, units will be allocated in the order indicated in Table 3.
randomize button: Used to generate random numbers for the valuation entries in the man sheet (Section 2.1). This button performs the same
operation as the randomize button in the man sheet.
clear result button: Used to clear the results shown on the result sheet.

3

Using Excel for Multi-Unit Auction 2

3.1

Getting Started

Initially, set the number of bidders n and the number of units k of thehomogeneous commodity with the maximum valuation x of the commodity.
These values are not set in the spreadsheets but rather within the Visual9

Table 3: Order of precedence for cases with multiple equivalent allocations
priority
12345678910assigned to bidder 10000111223assigned to bidder 20123012010

assigned to bidder 33210210100

Basic for Applications (VBA) window. In Figure 4, the default values are 3bidders (num man=3), 5 units (num item=5), and the maximum valuation ofthe commodity is 300 (max value of item=300).
See Figure 5.
1. From the Development ribbon, click on the Visual Basic button.
2. From Project Explorer, open Module 1 within Standard Modules
inside VBAProject (vcg-gba.xlsm).
3. Enter values for Const num man (number of bidders) and Const num item
(number of units) on the first and second lines, respectively, within
Module 1. Further, enter the value for Const max value of item (maximum valuation of the commodity) there.
4. Save the file and close the Visual Basic window.

3.2

Running the program

1. Set the initial conditions for the number of bidders and the number ofunits within the VBA window (Section 3.1).
2. In the man sheet (Section 2.1), enter valuations and bids for all bidders
for all units. In particular, be careful not to leave any bid unspecified.
3. Open the result sheet (Section 2.2) and click on the run VCG or run
GBA button.
10Figure 4: Visual Basic window

3.3

Results

The results computed by the VCG mechanism are shown in Columns A–E
within the result sheet. In the same sheet, the results computed by the GBA
are shown in Columns F–I (Section 2.2). See Section 2.2 for an explanationof the significance of each column.

11Figure 5: Visual Basic button

3.4

Optional: Generating random input data

This software is equipped with the optional capability to use random numbers
to conduct simulations. Click on the randomize button in either the man sheet(Section 2.1) or the result sheet (Section 2.2) to generate random values forthe valuations of all bidders for all numbers of units. The numbers of bidders
and of units will remain equal to the values specified as initial conditions(Section 3.1).

4

Troubleshooting

In what follows, we discuss remedies for some common problems that may
prevent proper execution of the program.

4.1

The Development ribbon is not displayed

The initial settings of Microsoft Oﬃce Excel are configured to omit displayof the Development ribbon. To display this ribbon, proceed as follows (forMicrosoft Oﬃce Excel 2016).
121. Click on the File ribbon.
2. Click on the Options button.
3. Within Excel Options, click on Ribbon User Settings.
4. Confirm that the Ribbon User Settings field is set to Main Tab,and then check the box for Development in the list of ribbons for theMain Tab (Figure 6).
5. Click on OK and close the Excel Options window. The Development button should be visible. (If not, close and re-open the Excelfile.)

Figure 6: Ribbon user settings

13

4.2

An error message appears when the program isrun

If error messages appear when attempting to run the program, check thefollowing points.
• All numerical input values (numbers of bidders and units in initial conditions (Section 3.1) and valuations and bids in the man sheet (Section2.1) are specified using double byte characters.
• The numbers of bidders and units specified in the initial conditions(Section 3.1) agree with the numbers of bidders and units listed in
the man sheet (Section 2.1). If the numbers do not agree, clicking on
the randomize button will automatically set the numbers to the values
specified in the initial conditions.
• There is no missing bid entry.

4.3

No error message appears, but the program cannot
compute a correct allocation

Check the following points, if no error message appears but the program does
not compute correct outcomes.
• Check whether you opened the program in Protected View (Figure 7).
If so, click on Enable editing (E) to terminate Protected View.
• Check that the numbers of bidders and units specified in the VBA
window (Section 3.1) agree with the numbers of bidders and units listed
in the man sheet (Section 2.1). If the numbers do not agree, clicking on
the randomize button will automatically set the numbers to the values
specified in the initial conditions.
• Check that there are no missing valuation entries.

14

Figure 7: Window as displayed when opened in Protected View

15