A few months ago, a client in the logistics sector came to us at Helion 360 with a straightforward-sounding request: they needed a structured, up-to-date database of Colombian suppliers, regulatory entities, and market pricing signals — all pulled from public web sources and delivered in Excel. What followed was a deep dive into the nuances of Colombian digital infrastructure, government portals, and the sometimes-frustrating world of web data extraction. Here's exactly how we approached it, what worked, and what we'd do differently.
Why Colombian Web Sources Are a Unique Challenge
Colombia has a rich ecosystem of public data — from the DANE (Departamento Administrativo Nacional de Estadística) portal to the RUES (Registro Único Empresarial y Social) business registry, to sector-specific sites like the Superintendencia de Industria y Comercio. But navigating these sources is not as clean as scraping, say, a U.S. government API.
Several factors complicate the process:
- Inconsistent data formats — Some portals publish PDFs, others use HTML tables, and a few offer CSV downloads hidden behind login walls.
- Spanish-language parsing — Field names, categorical values, and date formats follow Spanish conventions, which breaks many out-of-the-box scraping templates.
- Dynamic JavaScript rendering — Several Colombian government and commerce sites load data via JavaScript, meaning simple HTTP requests return empty shells.
- Session-based access — RUES and similar platforms require authenticated sessions, adding another layer to automation.
Understanding these constraints upfront saved us enormous rework time. Before writing a single line of code, we audited every target source manually.
The Stack We Used
Our extraction pipeline for this project combined a few key tools:
- Python (requests + BeautifulSoup) for static HTML pages — fast, reliable, and easy to adapt for Spanish character encoding (UTF-8 and ISO-8859-1 are both common in Colombian sites).
- Selenium with ChromeDriver for JavaScript-heavy portals — we used headless Chrome to render pages before parsing the DOM.
- Pandas for data cleaning and transformation — normalizing Colombian date formats (dd/mm/yyyy), stripping accented characters where needed, and structuring the output.
- OpenPyXL to write the final Excel file — with proper column headers, data validation, and formatted sheets for different source categories.
For clients who don't want to manage code, we've also done versions of this using Power Query in Excel directly, which I'll cover below. But the Python pipeline gave us far more control for this particular engagement.
Step-by-Step: How the Extraction Actually Worked
Step 1 — Source Mapping and Prioritization
We listed every web source the client needed data from, ranked by update frequency and reliability. DANE was top priority for macroeconomic indicators. RUES was essential for company verification. Sector-specific chambers of commerce (like the Cámara de Comercio de Bogotá) were useful for local supplier discovery.
Step 2 — Building Individual Scrapers Per Source
No one-size-fits-all scraper exists here. Each source got its own script. For DANE's statistical tables, we used direct CSV download links where available — much cleaner than parsing HTML. For commerce registries, we had to replicate the search form submission via POST requests and then parse the resulting table.
One lesson: always inspect the network tab in Chrome DevTools before writing your scraper. Many sites that look complex are actually calling a clean JSON API in the background — and you can hit that API directly instead of parsing HTML at all.
Step 3 — Cleaning and Normalizing the Data
Raw data from Colombian government sites is messy. We encountered NIT numbers (Colombia's business tax ID) formatted inconsistently — sometimes with hyphens, sometimes without. City names had regional spelling variants. We built a normalization dictionary for the most common inconsistencies and ran every dataset through it before merging.
Pandas made deduplication straightforward: once we had a consistent NIT field, we could merge datasets from multiple sources without creating duplicate company rows.
Step 4 — Writing to Excel with Structure
The final deliverable wasn't just a flat dump. Using OpenPyXL, we structured the workbook with multiple sheets: one per data source, plus a master consolidated sheet. We applied column widths, freeze panes on the header row, and dropdown validation on categorical columns like department (Cundinamarca, Antioquia, Valle del Cauca, etc.).
This made the file immediately usable by the client's non-technical team — no further cleanup needed.
Doing It with Power Query (No-Code Option)
If you're not comfortable with Python, Excel's Power Query is a legitimate alternative for simpler Colombian web sources. Go to Data → Get Data → From Web, paste the URL, and Excel will attempt to parse available tables. This works surprisingly well for DANE's published statistical tables and some chamber of commerce directories.
The limitation is refresh automation — Power Query requires manual refresh unless you're using Power BI or have a scheduled task set up. For clients who need daily updates, the Python pipeline is more robust.
Compliance and Ethical Considerations
Before any scraping project, we review the target site's terms of service and robots.txt. In Colombia, Law 1581 of 2012 (data protection) applies to personal data, and scraping personally identifiable information without consent carries legal risk. For this project, we focused on entity-level business data — company names, NITs, addresses, sectors — which is public registry information. We also rate-limited our requests to avoid overloading government servers.
If your use case involves individual personal data, consult a Colombian legal advisor before proceeding.
What We'd Improve Next Time
Looking back, two things would have saved us time. First, building a centralized source configuration file (a JSON or YAML file defining each source's URL, extraction method, and field mapping) would have made onboarding new sources much faster. Second, implementing a simple logging system from day one would have made debugging failed requests easier — we added it mid-project and wished we hadn't waited.
The result was a live, refreshable Excel database with over 4,000 validated Colombian company records across six industry sectors — delivered in three weeks. The client now runs quarterly refreshes using the same pipeline with minimal intervention.
If you're tackling a similar project — whether it's competitive intelligence, supplier mapping, or regulatory monitoring across Colombian digital sources — the methodology above gives you a solid foundation. The complexity is manageable when you break it into source-specific modules and invest in data normalization early.