Finding eBay Items with Python and Exporting to Excel

Why Build an eBay Search Tool?

If you collect items like entertainment memorabilia or retro gaming consoles, you know that finding specific listings can be time-consuming. This Python program automates that search by connecting to the eBay API, retrieving listings based on your chosen keyword, and exporting the results to an Excel file. This tool makes it easy to keep track of items, compare prices, and analyze data from your search. This is also good if you are tracking potential items to resell for a profit.

If you want the full code, you can get it here on GitHub.

Overview of the Program Structure

This tool consists of several functions:

1. API Request: Retrieves search results from eBay’s API based on a keyword.

2. Data Parsing: Extracts and processes relevant item details.

3. Excel Export: Saves results to an Excel file with conditional formatting for easy readability.

Each part of the program contributes to a seamless experience, from fetching data to organizing it in a user-friendly format.

Key Steps in the Code

To run this eBay search tool program, you'll need to install the following Python libraries:

1. requests - For making HTTP requests to the eBay API.

2. pandas - For organizing data and exporting it to an Excel file.

3. xlsxwriter - For Excel file creation with conditional formatting (used by pandas when saving to Excel).

4. python-dateutil - For parsing date strings from eBay’s API response.

5. pytz - For handling timezone conversions (e.g., converting to Central Time).

You can install all these libraries using the following pip commands:

pip install requests pandas xlsxwriter python-dateutil pytz

1. Setting Up API Requests

The program starts by setting up an API request to eBay’s Finding API. Here’s how it works:

def get_api_response(app_id, api_endpoint, params):
    response = requests.get(api_endpoint, params=params)
    print("Status Code:", response.status_code)  # Debugging statement

    if response.status_code != 200:
        print(f"HTTP Error: {response.status_code}")
        exit()

    try:
        data = response.json()
    except ValueError as e:
        print("Error parsing JSON response:", e)
        exit()

    return data

Explanation:

• The get_api_response function sends a GET request to eBay’s API, using parameters defined later in the code.

• The response.status_code check ensures the request was successful. If not, an error message is printed, and the program exits.

• The try block attempts to parse the JSON response, handling any issues if the response isn’t in JSON format.

The function returns the raw JSON data, which is parsed in the next step.

2. Parsing the Response Data

Once the JSON data is retrieved, the program needs to extract item details. The parse_items function accomplishes this:

def parse_items(data):
    if 'findItemsAdvancedResponse' in data:
        response_data = data['findItemsAdvancedResponse'][0]
        if 'errorMessage' in response_data:
            errors = response_data['errorMessage'][0]['error']
            for error in errors:
                print(f"Error Code: {error['errorId'][0]}, Message: {error['message'][0]}")
            exit()
        else:
            items = response_data['searchResult'][0].get('item', [])
            items = items[:50]  # Limit to 50 items
            if not items:
                print("No items found matching your criteria.")
                exit()
            return items
    else:
        print("Unexpected response format.")
        exit()

Explanation:

• This function navigates through the JSON structure to find items in the search results.

• It checks for error messages within the response and exits if any are found.

• Only the first 50 items are stored to avoid overwhelming the dataset and improve performance.

3. Processing Item Details

For each item, we process specific details like the title, price, condition, and listing type, then prepare them for export:

def process_item(item):
    title = item.get('title', ['N/A'])[0]
    url = item.get('viewItemURL', ['N/A'])[0]

    # Extract price and currency
    price_info = item.get('sellingStatus', [{}])[0].get('currentPrice', [{}])[0]
    price = price_info.get('__value__', 'N/A')
    currency = price_info.get('@currencyId', 'N/A')

    # Extract shipping cost
    shipping_info = item.get('shippingInfo', [{}])[0]
    shipping_cost_info = shipping_info.get('shippingServiceCost', [{}])[0]
    shipping_price = shipping_cost_info.get('__value__', 'N/A')
    if shipping_price in ['0.0', '0.00', '0']:
        shipping_price = 'FREE'
    elif shipping_price != 'N/A':
        shipping_price = f"{float(shipping_price):.2f}"

    # Extract listing type and condition
    listing_type = item.get('listingInfo', [{}])[0].get('listingType', ['N/A'])[0]
    condition = item.get('condition', [{}])[0].get('conditionDisplayName', ['N/A'])[0]

    # Format end time in CST
    end_time_str = item.get('listingInfo', [{}])[0].get('endTime', [''])[0]
    if end_time_str:
        end_time_utc = parser.parse(end_time_str)
        cst = pytz.timezone('US/Central')
        end_time_cst = end_time_utc.astimezone(cst)
        end_time_formatted = end_time_cst.strftime('%Y-%m-%d %I:%M:%S %p')
    else:
        end_time_formatted = 'N/A'

    return {
        'Title': title,
        'Price': price,
        'Currency': currency,
        'Shipping Price': shipping_price,
        'Listing Type': listing_type,
        'Item Condition': condition,
        'End Time': end_time_formatted,
        'URL': url
    }

Explanation:

• Each item’s title, URL, price, currency, shipping cost, listing type, condition, and end time are extracted.

• The end time is converted to Central Time (CST) and formatted for easy reading.

• The processed data is returned as a dictionary, making it easy to work with later when exporting.

4. Saving Results to Excel with Conditional Formatting

After processing, the results are saved to an Excel file. Here, conditional formatting highlights specific listing types (e.g., "Auction" vs. "FixedPrice"):

def save_to_excel(results, filename='ebay_listings.xlsx'):
    df = pd.DataFrame(results)
    if df.empty:
        print("No data to save to Excel.")
        exit()
    else:
        df = df[['Title', 'Price', 'Currency', 'Shipping Price', 'Listing Type',
                 'Item Condition', 'End Time', 'URL']]

        with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='Sheet1')
            workbook = writer.book
            worksheet = writer.sheets['Sheet1']

            # Define conditional formatting
            green_format = workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'})
            yellow_format = workbook.add_format({'bg_color': '#FFEB9C', 'font_color': '#9C6500'})

            # Apply formatting to 'Listing Type' column
            listing_type_col_idx = df.columns.get_loc('Listing Type')
            listing_type_col_letter = chr(65 + listing_type_col_idx)
            cell_range = f'{listing_type_col_letter}2:{listing_type_col_letter}{df.shape[0] + 1}'

            worksheet.conditional_format(cell_range, {'type': 'text', 'criteria': 'containing', 'value': 'FixedPrice', 'format': green_format})
            worksheet.conditional_format(cell_range, {'type': 'text', 'criteria': 'containing', 'value': 'Auction', 'format': yellow_format})

            # Adjust column widths
            for idx, col in enumerate(df.columns):
                max_len = df[col].astype(str).map(len).max()
                worksheet.set_column(idx, idx, max_len + 5)

            print(f"Data saved to {filename} with conditional formatting.")

Explanation:

• The data is saved to an Excel file using pandas and the xlsxwriter engine.

• Conditional formatting is applied to the "Listing Type" column, with green highlighting for "FixedPrice" and yellow for "Auction."

• Column widths are adjusted based on the maximum data length for improved readability.

Practical Use Cases and Customization Ideas

This program provides a helpful way to search for specific eBay items and organize the results. Here are a few ways to customize it further:

• Change the Keyword: Easily adjust the search keyword to look for different types of items.

• Set Custom Price Filters: Add filters to limit results by price range, allowing you to target specific budget levels.

• Add More Columns: Extract additional item details (like seller rating or shipping options) for more robust reporting.

With this tool, you can search for collectible items, track their prices, and manage your collection more efficiently.