How to Identify Last-Minute Bids | The Auction Software Review

Free Membership

* Receive software news
* Get exclusive discounts
* Post in the forum

Already a member? Login

Sell Online with Vendio's Free Ecommerce Platform

Online Auction Software
RainWorx Auction Software
Create your own Online Auction Website!
Plus Reverse, RFQ, Classifieds, Trades, & MORE

e-commerce design
e-commerce solutions
for all business types
at Solid Cactus

Home » Articles » Statistics You Won't Find on eBay » How to Identify Last-Minute Bids

Instructions
ASP Code
HTML Code
SQL Code

Instructions

Create an ASP page, inserting the ASP code below at the very start of the page. You will need to create a database table to hold the results (see below) and enter a valid connection string in the variable indicated.

Insert the HTML code below between the <body> tags of your ASP page. To use the page, enter the row numbers to process and click submit. The number of pages that you will be able to parse in one run will vary depending on your script timeout settings and bandwidth.

Use the SQL code to create queries in your database which aggregate the data you have extracted. Note that a full solution using SQL alone is not available - you will need to analyze the data in Excel to identify the correct winning bids because the last bid is not always the winning bid.

Please note the following:

No warranties or guarantees are offered for this code. It has been tested and used by us, but your system settings, eBay changes, and other factors may stop it working for you.
You use this code at your own risk. We are not responsible for any damage or complaint arising from its use.
You may freely use and adapt this code for any purpose, as you see fit. Please credit this site if you do so.

ASP Code


  <%
  
  'Allow 120 seconds for the script to run
  '***********************************************************
  Server.ScriptTimeout = 120

  'This requires that a sample of eBay auctions has already
  'been extracted into tblEbayItems.
  '
  'Modify tblEbayItems to add a field for the Date and Time
  'the auction finished, called DateTimeEnded
  '
  'You will also need a database table with the structure below,
  'or an equivalent. Create a foreign key constraint on ItemID
  'to the tblEbayItems field of the same name
  '
  'It is assumed that tblEbayItems has a sequentially 
  'numbered primary key, starting from the number defined in 
  'intFirstID below
  '***********************************************************
  'CREATE TABLE [tblEbayBids] (
  '[BidID] [int] IDENTITY (1, 1) NOT NULL ,
  '[ItemID] [int] NOT NULL ,
  '[BidAmount] [money] NOT NULL ,
  '[BidTime] [datetime] NOT NULL )
  
  Dim strDBConnection, objXMLHttp, strURL, strResponse, Match
  Dim Matches, strOutPut, strSQL, con, intRowFrom, intRowTo
  Dim intItemIDs(), rstItems, counter, intItemsCount, reBid
  Dim reEnded
  Const intFirstID = 31
  
  '***********************************************************
  'Set the variable strDBConnection to connect to your database
  '***********************************************************
  strDBConnection = "ENTER YOUR CONNECTION STRING HERE"
  
  '***********************************************************
  'Get the table row of tblEbayItems to start from and end at
  '***********************************************************
  intRowFrom = CLng(Request.Form("rowfrom"))
  intRowTo = CLng(Request.Form("rowto"))
  
  '***********************************************************
  'Check that row numbers have been submitted before processing
  '***********************************************************
  If intRowFrom > 0 And intRowTo > 0 Then 
  
    '***********************************************************
    'Read the database and eBay IDs into an array
    '***********************************************************
    ReDim intItemIDs(intRowTo - intRowFrom, 1)
    strSQL = "SELECT ItemID, eBayItemID, Bids FROM tblEbayItems" & _
      " WHERE ItemID >= " & intRowFrom + intFirstID - 1 & " AND" & _
      " ItemID <= " & intRowTo + intFirstID - 1

    counter = 0
    
    'the recordset is opened as forward-only and read-only
    Set rstItems = Server.CreateObject("ADODB.Recordset")
    rstItems.Open strSQL, strDBConnection, 0, 1
    
    Do Until rstItems.EOF
      If rstItems.Fields("Bids") > 0 Then
        'only add if there are bids
        intItemIDs(counter, 0) = rstItems.Fields("ItemID")
        intItemIDs(counter, 1) = rstItems.Fields("ebayItemID")
        counter = counter + 1
      End If
      rstItems.MoveNext
    Loop
    
    intItemsCount = counter - 1
    
    rstItems.Close()
    Set rstItems = Nothing
  
    '**********************************************************
    'Initialise the database connection
    '**********************************************************
    Set con = Server.CreateObject("ADODB.Connection")
    con.Open strDBConnection
    
    '***********************************************************
    'Set up regular expression objects
    '***********************************************************
  
    'The reBid object matches each bid placed
    'The "$1" placeholder matches the price and the "$2"
    'placeholder matches the date and time of the bid
    '***********************************************************  
    Set reBid = New RegExp
    reBid.Global = True
    reBid.IgnoreCase = True
    reBid.Pattern = "<td[^>]*>.*Display bid[^\$]*\$([\d\.\,]*)." & _
      "*\n.*time of bid[^>]*>([\w\d\-: ]{18}) PST"
    
    'The reEnded object matches the date and time the auction
    'ended using the "$1" placeholder 
    '***********************************************************  
    Set reEnded = New RegExp
    reEnded.Global = True
    reEnded.IgnoreCase = True
    reEnded.Pattern = "<font size=""2"">.*\nEnds.*\n<\/font>.*\" & _
      "n<\/td>.*\n<td colspan=""4"">([\w\d\-: ]{18}) PST"
    
    '**********************************************************
    'Loop through the array and get the eBay bids page for each
    'item
    '**********************************************************
    For counter = 0 To intItemsCount
      strURL = "http://offer.ebay.com/ws3/eBayISAPI.dll?ViewBid" & _
        "s&item=" & intItemIDs(counter, 1)
      
      Set objXMLHttp = Server.CreateObject("MSXML2.ServerXMLHTTP.4.0")
      objXMLHttp.Open "GET", strURL, False
      objXMLHttp.Send
      strResponse = objXMLHttp.ResponseText
      Set objXMLHttp = Nothing
      
      '**********************************************************
      'Run the end time regular expression and update the database
      '**********************************************************
      Set Matches = reEnded.Execute(strResponse) 
        For Each Match in Matches
          
          strOutput = strOutPut & "<tr><td colspan='2'>ItemID: " & _
            intItemIDs(counter, 0) & ", eBayID: " & _
            intItemIDs(counter, 1) & " - ENDED: " & _
            reEnded.Replace(Match.Value, "$1") & "</td></tr>" & _ 
            vbNewLine

          strSQL = "UPDATE tblEbayItems SET DateTimeEnded = '" & _
            FormatDateTime(reEnded.Replace(Match.Value, "$1"),1) & _
            " " & _
            FormatDateTime(reEnded.Replace(Match.Value, "$1"),3) & _
            "' FROM tblEbayItems WHERE ItemID = " & _ 
            intItemIDs(counter, 0)

          con.Execute strSQL
        Next
      
      '**********************************************************
      'Execute the bids regular expression and add bids to database
      '**********************************************************
      Set Matches = reBid.Execute(strResponse) 
        For Each Match in Matches
          strOutput = strOutPut & reBid.Replace(Match.Value, "<tr>" & _
            " <td>AMOUNT: $1</td> <td>DATETIME: $2</td> </tr>" & _
            vbNewLine)

          strSQL = reBid.Replace(Match.Value, "INSERT INTO " & _
            "tblEbayBids (ItemID, BidAmount, BidTime) VALUES (" & _ 
            intItemIDs(counter, 0) & ",CAST ('$1' AS MONEY),'" & _
            FormatDateTime(reBid.Replace(Match.Value, "$2"),1) & " " & _
            FormatDateTime(reBid.Replace(Match.Value, "$2"),3) & "')")

          con.Execute strSQL
        Next
        
    Next
      
    con.Close
    Set con = Nothing
    
    strOutput = "<tr><td colspan='2'>Rows " & intRowFrom & " to " & _ 
      intRowTo & " complete</td></tr>" & strOutput
    
    '**********************************************************
    'Increment the row numbers for the next group of auctions
    '**********************************************************
    intItemsCount = intRowTo - intRowFrom
    intRowFrom = intRowTo + 1
    intRowTo = intRowTo + intItemsCount + 1
    
  Else:
  
    '**********************************************************
    'the initial row range - increase or decrease depending on 
    'connection speed
    '**********************************************************
    intRowFrom = 1
    intRowTo = 25
  
  End If
  
  %>

HTML Code

  <form name="form" method="post" action="">
    From: 
    <input name="rowfrom" type="text" value="<%=intRowFrom%>"
      size="5" maxlength="5">
    <br>
    To: 
    <input name="rowto" type="text" value="<%=intRowTo%>" 
      size="5" maxlength="5">
    <br>
    <br>
    <input name="submit" type="submit" value="Submit">
  </form>
  <br>
  <table><%=strOutPut%></table>
  <br>

SQL Code

This finds the last bid placed on each auction (remember that this is not always the winning bid), call it "qryLastBidTimes":

   SELECT ItemID, MAX(BidTime) AS LastBidTime
   FROM tblEbayBids
   GROUP BY ItemID

This uses the previous query and tblEbayItems to calculate the difference in seconds between the last bid placed and the auction end time:

   SELECT tblEbayItems.ItemID, 
     DATEDIFF(s, qryLastBidTimes.LastBidTime, tblEbayItems.DateTimeEnded)
     AS LastBidGap
   FROM tblEbayItems INNER JOIN
     qryLastBidTimes ON tblEbayItems.ItemID = qryLastBidTimes.ItemID
   WHERE 
     DATEDIFF(s, qryLastBidTimes.LastBidTime, tblEbayItems.DateTimeEnded) 
       <= 60)
   ORDER BY 
     DATEDIFF(s, qryLastBidTimes.LastBidTime, tblEbayItems.DateTimeEnded)

Contents

Instructions

ASP Code

HTML Code

SQL Code