From RapNet to Excel: Automating Diamond Listing Exports Using Python

# From RapNet to Excel: Automating Diamond Listing Exports Using Python

📌 **Problem**: We were manually filtering, copying, and structuring diamond listing data from RapNet across multiple size, color, and clarity combinations — wasting hours every week.

💡 **Solution**: I built a Python automation tool that connects to the RapNet API, applies dynamic filters, and exports clean Excel files for each filter combo.

This blog breaks down the tech behind the tool, how it works, and why it’s now a core part of our internal process.

---

## 🚀 What This Tool Does

- Connects to RapNet's API using secure token authentication
- Applies custom filters: saved search ID, size, color, clarity
- Parses JSON responses into structured datasets
- Outputs multi-sheet Excel files — each sheet represents a unique combination

---

## 🧠 Architecture Overview

```plaintext
+----------------------+       +----------------------+
|  market_input.txt    | ----> |  Filter Controller   |
|  (config file)       |       |  (Python logic)      |
+----------------------+       +----------------------+
                                     |
                             Fetch Saved Searches
                                     ↓
                          Apply filters: Size, Color, Clarity
                                     ↓
                          Fetch Listings from RapNet API
                                     ↓
                           Extract, transform, and structure
                                     ↓
                        Export Excel using pandas + openpyxl

---

🛠️ Tech Stack

---

🧪 Sample Config Input (market_input_sample.txt)

load_saved_search = EMERALD LG (GD)
size_range = 1.50:1.69
colors = D,E,F,G,H,I,J,K,L,M
clarities = IF,VVS1,VVS2,VS1,VS2,SI1,SI2
token = YOUR_API_TOKEN_HERE

⚠️ token must be kept private — never commit this file to GitHub. Use .gitignore.

---

📂 Sample Output

Each Excel file is named using the filter set and timestamp, e.g.:

EMERALD_1.50_1.69_2025-04-24_14-22-01.xlsx

Each sheet in the file contains:

• Diamond attributes (cut, polish, symmetry, color, clarity, lab comment)

• Tabular listing data with prices, sizes, and measurements

---

💡 Technical Highlights

• Handled nested JSON structures with extract_nested_data()

• Modularized filter building, separating saved search logic from the fetcher

• Exported Excel files with multiple dynamic sheets, mapped using filter keys

• Implemented fallback logic for key errors and empty payloads

---

📦 Real-World Value

• ✅ Saved 8–10 hours/week in manual searching and formatting

• ✅ Enabled batch downloads of listings with multiple filter sets

• ✅ Provided reusable, config-driven scripts for other team members

---

🔗 Live Project

• GitHub: RapNet Diamond Exporter

• Medium Blog: How I Automated Diamond Data Extraction from RapNet Using Python and Excel

---

🔄 Next Steps

• Convert the script into a Streamlit UI

• Add logging & error reporting via logging

• Integrate with email service for automated weekly reports

---

👨‍💻 About Me

I’m a Data Analyst at Sheetal Manufacturing Co.
Currently pursuing MSc in Data Science from Symbiosis.
I enjoy automating real-world workflows with clean, scalable Python code.

🔗 Connect with me on LinkedIn

---

🙌 Let’s Collaborate

If you liked this, feel free to:

• Fork the repo

• Suggest improvements

• Drop a comment — I’d love to connect with fellow automation nerds 😄

#Python #API #Automation #Excel #DataEngineering #RapNet #PortfolioProject