DEV Community: Mohammad Ehsan Ansari

I Tried Cleaning 5,000+ Rows of Messy Excel Data… and Realized the Problem Is Bigger Than I Thought

Mohammad Ehsan Ansari — Tue, 09 Dec 2025 06:39:00 +0000

I’ve spent the last few months cleaning spreadsheets — not because I love it (I don’t), but because I was building something that required understanding how bad real-world data actually is.

If you’ve ever worked with CSVs exported from different tools, you already know the pain:

male, Male, MALE, m
₹20,000, 20k, 20000.00, ₹ 20,000/-
Dates like 12/03/24, 2024-03-12, and 03-12-2024 all in the same sheet
Category columns where “Electronics”, “Electronic”, “Elec.” all refer to the same thing
Rows where values literally belong to different columns
Two sheets that should merge cleanly… but don’t

None of this is new. But when you try automating it, you realize something:

Cleaning data isn’t “just clean the data.”

It’s a chain of dependent decisions.

For context, I’m building a tool called RowTidy — an AI-powered data cleaning system — mostly because of these discoveries.

🔗 https://rowtidy.com

This post is not promotional — it’s the story of what I learned while trying to make sense of messy spreadsheets.

🧩 1. No two spreadsheets are ever the same

Even inside the same organization, I’ve seen:

Qty, quantity, QTY, QTY. , QTY ( )
Customer Name, Client Name, Name
Price, Rate, Cost, Total Price

Spreadsheet headers evolve based on:

who created them
which department exported them
which software wrote them
how fast the person writing it was in a rush

Spreadsheets reflect human chaos.

🧹 2. Hardcoded rules always break on the next file

You can write rules:

trim spaces
fix capitalization
remove symbols
normalize dates
normalize currency

…but then you get a file that uses one of those “dirty” formats intentionally.

Or a system where L means lakhs, but another team uses k.

Or numerical fields that sometimes contain text — but intentionally.

Static rules don’t work.

Dynamic rules do.

🤖 3. LLMs help — but passing the entire sheet is a disaster

If you pass 5,000 rows:

❌ hallucinations

❌ inconsistent corrections

❌ smashed formats

❌ some rows get cleaned differently than others

But if you pass:

20–30 representative rows
column types
sheet type (Sales Sheet / Product Catalog / Property Listing etc.)
patterns in the data

LLMs suddenly behave like extremely consistent rule generators.

They say things like:

“Column Price seems to contain currency in mixed formats. Normalize as plain float with 2 decimals.”
“Column Category contains variants. Canonicalize based on semantic similarity.”
“Column BHK in this dataset is numeric but appears in words. Convert consistently.”

Then you take those rules and deterministically apply them across the whole dataset.

This works far better than the naïve “ask LLM to clean the whole sheet.”

🔄 4. The hardest part: conflicts & dependencies

Example:

Name	Gender
Ehsan	Male
Ehsan	Female

Which one is correct?

Or:

Product	Category
Mouse	Accessories
Mouse	Peripheral

Or dates like:

02/03/24 (DD/MM/YY?)
02/03/24 (MM/DD/YY?)

The only reliable way I found:

Detect potential conflicts
Ask AI to propose resolutions
Let the user approve or discard
After approval, future sheets auto-resolve the same conflict

Human-in-the-loop.

But only when necessary.

🧪 5. Trying to merge two sheets is where everything truly breaks

A friend shared this experience:

“We receive a sheet every hour with 3–4K rows.

It looks similar… but merging it with our master sheet always breaks.”

Why?

Because two seemingly identical sheets may have:

different casing
different punctuation
different abbreviations
different spacing
different numeric formats
different canonical values

The data is “the same” — but not identical.

This is exactly the kind of problem that made me build RowTidy:
https://rowtidy.com

🎯 What I learned

Data cleaning is not about formulas.

It’s not about regex.

It’s not even about machine learning.

It’s about:

understanding intent
catching inconsistency
normalizing semantics
detecting dependencies
handling edge cases without destroying valid data
doing all this without annoying the user

The more spreadsheets I cleaned, the more I realized:

The problem isn’t that data is messy.

The problem is that every dataset is messy in a different way.

And that’s exactly why automation is both difficult and meaningful.

If you deal with messy CSVs, duplications, conflicting columns, or imports that constantly break — I’d love to hear about your workflow.

Also, if you're curious, you can check what I’m building here:

👉 https://rowtidy.com

What is Ctrl+H in Excel? Master Find & Replace for Data Cleaning

Mohammad Ehsan Ansari — Sun, 16 Nov 2025 12:05:40 +0000

Ctrl+H opens Excel’s Find and Replace dialog — a commonly used tool for locating and updating values in bulk. It is helpful for tasks like correcting text, formatting, or cleaning large datasets quickly.

What Ctrl+H Does

By pressing Ctrl+H, you can:
• Search for specific text, characters, or numbers
• Replace values individually or in bulk
• Search inside formulas, values, or comments
• Use wildcards to match patterns
• Work within a selected range, sheet, or full workbook

Basic Usage

1.  Press Ctrl+H
2.  Enter text under Find what
3.  Enter text under Replace with
4.  Use Replace or Replace All

Example:
Replace "USA" → "United States" across a sheet.

Advanced Options

Option	Description
Match Case	Finds text with same capitalization
Match Entire Cell Contents	Replaces only when full cell matches
Search Within	Selection / Sheet / Workbook
Look In	Formulas / Values / Comments

Common Data Cleaning Examples

Task	Find	Replace	Notes
Remove extra spaces	" "	" "	Run multiple times until no changes
Standardize abbreviations	"St."	"Street"	Useful for addresses
Format dates	"/"	"-"	Changes separator
Remove characters	"@"	empty	Deletes unwanted symbols
Remove commas in numbers	","	""	Useful before numeric conversion

Wildcards Table

Wildcard	Meaning	Example Input	Notes
*	Matches any characters	John *	Replaces variable endings
?	Matches one character	???	For fixed length patterns
~	Escape character	~*	Finds literal asterisk

Sample Cleaning Workflow

Step 1 — Reduce Extra Spaces

Repeat Ctrl+H using " " → " " until no more replacements.

Step 2 — Standardize Terms

Use Match Case if needed (e.g., inc. → Inc.).

Step 3 — Format Dates

Find / → Replace with - (Look in: Values).

Shortcuts Inside the Dialog

Action	Shortcut
Open Find & Replace	Ctrl+H
Find Next	Alt+F
Replace	Alt+R
Replace All	Alt+A
Close dialog	Esc

Tips & Mistakes

Tips
• Use Find Next to verify before replacing
• Keep a backup copy
• Limit scope using selection
• Use wildcards for patterns

Mistakes to Avoid
• Replacing without previewing
• Unintentionally modifying formulas
• Ignoring case sensitivity
• Running across entire workbook without intent

When to Use Ctrl+H vs Other Tools

Method	Best Use Case	Limitations
Ctrl+H	Simple bulk replacements & quick text formatting	Not ideal for conditional or complex patterns
Formulas	Logic-based, conditional replacements	Creates new columns, needs cleanup later
Power Query	Large structured transformations	Setup time & learning curve
RowTidy.com	Pattern learning, repetitive multi-sheet cleaning	External tool, not native Excel

Note: RowTidy.com is an external tool that helps automate data-cleaning workflows. It can be useful for complex, repetitive, or multi-file cleanup situations when manual Find & Replace becomes time-consuming.

Conclusion

Ctrl+H is a fast and effective option for bulk editing and data cleanup in Excel. It works best for straightforward replacements and formatting updates. For more advanced, repetitive, or large-scale cleaning tasks, additional tools, including Excel formulas, Power Query, or AI-based platforms like RowTidy.com, can help streamline the process.

Cleaning Supplier Excel Sheets: Best Practices for Small Businesses

Mohammad Ehsan Ansari — Wed, 12 Nov 2025 11:58:37 +0000

Cleaning Supplier Excel Sheets: Best Practices for Small Businesses

For small businesses, supplier Excel sheets are the backbone of inventory management, pricing, and purchasing.

But here’s the problem: these files are often messy, inconsistent, and hard to work with.

From merged cells to inconsistent headers, cleaning supplier spreadsheets can feel like an endless chore.

This guide outlines best practices small businesses can use to clean supplier data efficiently — without wasting valuable time.

🛑 Why Supplier Excel Sheets Are Always Messy

Different Formats Per Supplier

→ One uses Product Name, another uses Item Description.
Merged Cells & Broken Headers

→ Makes sorting, filtering, and formulas unreliable.
Mixed Currencies

→ INR, USD, GBP, EUR scattered across sheets.
Duplicated SKUs

→ Same product listed in multiple ways.
Manual Updates

→ Suppliers send monthly updates in different templates.

✅ Best Practices for Cleaning Supplier Excel Sheets

1. Standardize Column Headers

Decide on a Golden Schema: SKU, Product Name, Category, Price, Currency, Stock.
Use consistent naming across all suppliers.

2. Normalize Dates & Numbers

Convert all dates to YYYY-MM-DD.
Format prices with 2 decimal places.
Strip units like "10 pcs" → 10.

3. Remove Extra Spaces & Hidden Characters

Use Excel’s TRIM() or automated tools to clean whitespace.
Remove line breaks or non-printable characters.

4. Handle Missing Data

Replace blanks with N/A or 0.
Flag missing SKU or price fields for review.

5. Merge Multiple Supplier Sheets

Use consistent schema so files can be merged without chaos.
Remove duplicates across suppliers.

6. Automate Repetitive Tasks

Save cleaning recipes you can reuse monthly.
Automate column mapping for each supplier.

📊 Example: Before & After

Before

| Item Code | Unit Cost | Qty | Currency |

|-----------|-----------|-----|----------|

| P001 | 10 pcs | USD | $20 |

| P002 | | 5 | INR |

After (Golden Schema)

| SKU | Product Name | Stock | Price (USD) |

|------|--------------|-------|-------------|

| P001 | N/A | 10 | 20.00 |

| P002 | N/A | 5 | 0.00 |

🤖 How RowTidy Helps Small Businesses

Instead of spending hours cleaning supplier sheets:

Upload messy Excel/CSV files.
AI automatically detects headers, trims spaces, removes duplicates, and normalizes formats.
Save mappings per supplier → one-click reuse.
Export to Excel, CSV, JSON, or even Google Sheets.

This means less time on admin, more time on growth.

📌 Conclusion

Supplier Excel sheets don’t need to be a bottleneck for small businesses.

By following these best practices (and using automation tools like RowTidy), you can cut cleanup time from hours to minutes and ensure your business runs smoothly.

✍️ Ready to stop wasting time on messy supplier files?

👉 Try RowTidy today and get your first month free.

Convert Blobs of Text into Rows in Excel Without Manual Work

Mohammad Ehsan Ansari — Wed, 03 Sep 2025 06:11:35 +0000

Convert Blobs of Text into Rows in Excel Without Manual Work

Have you ever received a spreadsheet where entire paragraphs of information are crammed into one cell? Instead of structured rows, you're left with blobs of text that are impossible to analyze.

Example:

Cell A1
`OrderID: 1234, Date: 2024-01-10, Customer: John Doe, Amount: $250`

What you really want is:

OrderID	Date	Customer	Amount
1234	2024-01-10	John Doe	250

This post will show you how to automatically split blobs of text into rows and columns in Excel — no manual copy-paste required.

🚨 Why Text Blobs Are a Problem

Impossible to analyze → Pivot tables and charts won't work.
Error-prone → Manual splitting leads to mistakes.
Time-consuming → Cleaning hundreds of rows by hand takes hours.
Inconsistent formatting → Vendor or export files rarely use the same separators.

🛠 Method 1: Text-to-Columns (Quick Fix)

Excel's built-in Text-to-Columns is the fastest option if your text is consistently separated by commas, semicolons, or tabs.

Steps:

Select the column with text blobs.
Go to Data → Text to Columns.
Choose Delimited → pick the right delimiter (comma, tab, etc.).
Click Finish.

✔️ Works well for consistent separators.

❌ Breaks if the text uses inconsistent patterns.

🛠 Method 2: Using Formulas (Flexible)

If text contains key-value pairs (like OrderID: 1234), you can extract fields with formulas.

For example, to pull the OrderID:

=MID(A1, FIND("OrderID:", A1) + 8, FIND(",", A1) - FIND("OrderID:", A1) - 8)

✔️ Customizable for structured text.
❌ Complex for large datasets.

🛠 Method 3: Power Query (Advanced)

Power Query is excellent for large blobs:

Go to Data → Get & Transform → From Table/Range.
Split the column by delimiter or by text pattern.
Transform into rows automatically.
Load back into Excel.

✔️ Handles thousands of rows.
❌ Steeper learning curve for beginners.

🤖 Method 4: Automate with RowTidy

If you often deal with vendor exports, invoices, or messy reports where blobs of text appear in single cells, RowTidy automates the cleanup:

Detects blobs of text inside cells
Splits structured fields into proper columns
Converts paragraphs into clean, row-based data
Exports directly into Excel/CSV/Google Sheets

Instead of wasting hours, just upload your messy file and get a structured dataset in seconds.

✅ Best Practices

Always backup your original data before splitting text.
Define a standard schema (e.g., always OrderID, Date, Amount).
Test splitting on a sample set first.
Use consistent delimiters in future exports if possible.

📌 Conclusion

Blobs of text in Excel are one of the biggest productivity killers. Instead of manually retyping or copy-pasting, use Text-to-Columns, formulas, Power Query, or RowTidy to turn messy text into rows.

👉 By automating this, you'll save hours every week and ensure your data is always analysis-ready.

✍️ Tired of splitting text blobs manually?

👉 Try RowTidy for AI-powered text-to-rows conversion and data cleaning.

Building Intelligent Agents with ScrapeGraph: The Complete Guide

Mohammad Ehsan Ansari — Tue, 24 Jun 2025 16:51:39 +0000

Empowering Intelligent Agents with ScrapeGraphAI

In today's rapidly evolving digital world, intelligent agents need immediate access to accurate and structured online data to make smart decisions. This is where ScrapeGraphAI comes in—transforming from a simple scraping tool into an essential component for agents. By integrating ScrapeGraphAI, your agents can automatically fetch, validate, and process web data in real time, bridging the gap between raw information and actionable insights.

Why Intelligent Agents Need ScrapeGraphAI

Intelligent agents depend on up-to-date data to:

Enhance Decision-Making: Accessing real-time web data enables agents to respond quickly to changing environments.
Optimize Automation: With structured data in hand, agents can automate workflows and execute tasks more efficiently.
Drive Innovation: Agents empowered by reliable data can unlock new insights, driving better strategies and competitive advantages.

Without a tool like ScrapeGraphAI, agents would struggle to access the wealth of data available on the internet—limiting their ability to learn, adapt, and make data-driven decisions.

How ScrapeGraphAI Becomes a Tool for Agents

ScrapeGraphAI not only automates web scraping but also integrates seamlessly with intelligent agent frameworks. It serves as a dedicated tool agents can invoke to fetch data whenever needed.

🔑 Key Features

Automated Data Extraction: ScrapeGraphAI handles the complexity of scraping and delivers structured data using predefined schemas.
Schema Validation: Ensures agents receive consistent and reliable information.
Tool Integration: Easily bind ScrapeGraphAI to your agent, enabling web scraping as part of its decision-making process.

🧠 Example Integration Code

import os
from dotenv import load_dotenv
from langchain_openai import ChatOpenAI
from langgraph.graph import MessagesState, START, StateGraph
from langgraph.checkpoint.memory import MemorySaver
from langchain_core.messages import AIMessage, HumanMessage, SystemMessage
from langgraph.prebuilt import tools_condition, ToolNode

load_dotenv()

def smart_scraper_func(prompt: str, source: str):
    from scrapegraph_py import SyncClient
    from scrapegraph_py.logger import get_logger
    get_logger(level="DEBUG")

    sgai_client = SyncClient(api_key=os.getenv("SCRAPEGRAPH_API_KEY"))
    response = sgai_client.smartscraper(website_url=source, user_prompt=prompt)
    print(f"Request ID: {response['request_id']}")
    print(f"Result: {response['result']}")
    sgai_client.close()
    return response

def search_scraper_func(prompt: str):
    from scrapegraph_py import Client
    from scrapegraph_py.logger import sgai_logger
    sgai_logger.set_logging(level="INFO")

    sgai_client = Client(api_key=os.getenv("SCRAPEGRAPH_API_KEY"))
    response = sgai_client.searchscraper(user_prompt=prompt)
    print(f"Request ID: {response['request_id']}")
    print(f"Result: {response['result']}")
    sgai_client.close()
    return response

tools = [smart_scraper_func, search_scraper_func]
llm = ChatOpenAI(model="gpt-4o", api_key=os.getenv("OPENAI_API_KEY"))
llm_with_tools = llm.bind_tools(tools)
sys_msg = SystemMessage(content="You are a helpful assistant tasked with performing scraping scripts with scrapegraphai. Use the tool asked from the user")

def assistant(state: MessagesState):
    return {"messages": [llm_with_tools.invoke([sys_msg] + state["messages"])]}

builder = StateGraph(MessagesState)
builder.add_node("assistant", assistant)
builder.add_node("tools", ToolNode(tools))
builder.add_edge(START, "assistant")
builder.add_conditional_edges("assistant", tools_condition)
builder.add_edge("tools", "assistant")
graph = builder.compile()

How It Fits into the Agent Workflow

ScrapeGraphAI becomes a module in your agent's toolkit. Instead of manually coding web data extraction every time, your agent can simply call this function to retrieve the latest data. This integration allows the agent to:

Automate Web Data Retrieval: Call the scraping tool on-demand during various tasks.
Process and Analyze Data: Use the structured output for further analysis or to trigger other actions.
Enhance Responsiveness: Make decisions based on current, accurate data pulled directly from the web.

Frequently Asked Questions

What are intelligent agents and how do they use ScrapeGraphAI?

Intelligent agents are:

Automated systems that make decisions
Use real-time data for insights
Integrate with tools like ScrapeGraphAI
Process and analyze web data
Adapt to changing conditions
Learn from interactions

How does ScrapeGraphAI enhance agent capabilities?

ScrapeGraphAI enhances agents by:

Providing structured web data
Enabling real-time data collection
Offering schema validation
Supporting multiple data sources
Automating data extraction
Ensuring data accuracy

What types of data can agents collect with ScrapeGraphAI?

Agents can collect:

Product information
Market trends
Competitor data
User reviews
Price data
Industry insights

How do I integrate ScrapeGraphAI with my existing agents?

Integration steps include:

Installing required packages
Setting up API authentication
Configuring data schemas
Implementing error handling
Setting up monitoring
Testing integration

What are the best practices for agent-based scraping?

Best practices include:

Implementing rate limiting
Using proper error handling
Validating extracted data
Monitoring agent performance
Maintaining data quality
Following platform policies

How can I scale my agent operations?

Scaling strategies include:

Using distributed processing
Implementing load balancing
Managing resource allocation
Optimizing data storage
Monitoring performance
Handling concurrent requests

What are common challenges in agent integration?

Common challenges include:

Data validation issues
Rate limiting concerns
Authentication handling
Error management
Performance optimization
Resource allocation

How do I handle errors in agent operations?

Error handling includes:

Implementing retry logic
Logging error details
Setting up alerts
Managing timeouts
Validating responses
Maintaining fallbacks

What security measures should I implement?

Security measures include:

API key protection
Data encryption
Access control
Audit logging
Error handling
Compliance monitoring

How can I monitor agent performance?

Monitoring includes:

Tracking success rates
Measuring response times
Monitoring resource usage
Analyzing error patterns
Checking data quality
Evaluating efficiency

What are the costs involved?

Cost considerations include:

API usage fees
Computing resources
Storage requirements
Maintenance costs
Development time
Monitoring tools

How do I maintain my agent system?

Maintenance tasks include:

Regular updates
Performance monitoring
Error checking
Data validation
System optimization
Documentation updates

What development skills are needed?

Required skills include:

Python programming
API integration
Data processing
Error handling
System architecture
Performance optimization

How can I ensure data quality?

Quality assurance includes:

Schema validation
Data cleaning
Error checking
Format verification
Consistency checks
Regular testing

What are the limitations of agent-based scraping?

Limitations include:

Rate limiting
Resource constraints
Platform restrictions
Data availability
Processing speed
Accuracy concerns

Conclusion

Integrating ScrapeGraphAI into your intelligent agents is a game changer. It provides a seamless bridge between the vast amount of web data and the sophisticated decision-making capabilities of your agents. With ScrapeGraphAI as a dedicated tool, your agents can operate with real-time information—driving innovation, efficiency, and strategic advantage.

Embrace ScrapeGraphAI, empower your agents, and unlock the true potential of data-driven automation.

Happy coding and innovating!

Related Resources

Want to learn more about intelligent agents and web scraping? Explore these guides:

Monitor Website Changes with Python, Streamlit, Slack & Olostep

Mohammad Ehsan Ansari — Tue, 06 May 2025 16:19:49 +0000

Souce code

🛠️ Monitor Website Changes with Python, Streamlit, Slack & Olostep

Keeping tabs on your competitors or your own brand pages is crucial — changes in pricing, content, or job openings can have a direct impact on your strategy. This blog shows how to monitor any public webpage for updates using:

✅ Python

✅ Streamlit

✅ Slack for alerts

✅ Olostep API for web scraping

🎯 What You’ll Learn

How to detect pricing updates, new jobs, copy changes, new articles/pages, logos, and more
How to use Olostep’s scraping API to retrieve website content
How to compare two content versions and find changes
How to notify your team via Slack when a change is detected
How to visualize and interact with the app using Streamlit

🔧 Step 1: Install Dependencies

Start by installing the necessary packages:

pip install streamlit requests python-dotenv slack_sdk difflib

You’ll also need:

A Slack bot token
Your Slack channel ID
Your Olostep API key

Create a .env file to securely store them:

OLOSTEP_API_KEY=your_olostep_key
SLACK_BOT_TOKEN=xoxb-your-slack-token
SLACK_CHANNEL_ID=your-channel-id

🌐 Step 2: Set Up Web Scraping with Olostep

We use Olostep’s /scrapes endpoint to fetch the raw HTML or markdown content of any URL.

def scrape_url(url):
    headers = {
        "Authorization": f"Bearer {os.getenv('OLOSTEP_API_KEY')}",
        "Content-Type": "application/json"
    }
    payload = {
        "formats": ["markdown"],
        "country": "US",
        "url_to_scrape": url,
    }
    response = requests.post("https://api.olostep.com/v1/scrapes", headers=headers, json=payload)
    return response.json()["result"]["markdown_content"]

🧠 Why markdown? It's easier to analyze than raw HTML and more structured for identifying changes.

🧠 Step 3: Compare New vs Old Content

We compare the newly scraped content with the last saved version using Python’s difflib.

def compare_versions(old_text, new_text):
    diff = list(difflib.unified_diff(
        old_text.splitlines(), 
        new_text.splitlines(), 
        lineterm="", 
        n=2
    ))
    return "
".join(diff)

We store the previous version in a local file (latest.md) and load it when the app runs again.

🔔 Step 4: Notify via Slack

When a change is detected, send a message to your Slack workspace:

from slack_sdk import WebClient

def send_slack_notification(change_text):
    client = WebClient(token=os.getenv("SLACK_BOT_TOKEN"))
    message = f"🔍 *Website Change Detected!*

{change_text[:3000]}

    client.chat_postMessage(channel=os.getenv("SLACK_CHANNEL_ID"), text=message)

📌 Slack messages are capped to ~3000 chars for readability. You can link to a diff file if needed.

🖼️ Step 5: Build the Streamlit App

We use Streamlit for a clean UI to trigger checks and view results.

st.title("🔎 Website Change Monitor")

url = st.text_input("Enter the website URL to monitor")

if st.button("Check for Changes"):
    new_content = scrape_url(url)

    if os.path.exists("latest.md"):
        old_content = Path("latest.md").read_text()
        diff = compare_versions(old_content, new_content)

        if diff:
            send_slack_notification(diff)
            st.success("Changes detected and sent to Slack!")
        else:
            st.info("No changes found.")
    else:
        st.info("First time check. Saving initial version.")

    Path("latest.md").write_text(new_content)

🧪 Example: Monitoring a Competitor's Pricing Page

Add your competitor's pricing page: https://example.com/pricing
Run the Streamlit app
It scrapes content, checks for diff, and posts to Slack if any changes are found
Useful for:
- Pricing wars
- Detecting seasonal offers
- New plans or structure

🧠 Use Cases

✅ Monitor your own site for accidental changes
✅ Keep an eye on competitors
✅ Detect new job postings on a careers page
✅ Track new testimonials, logos, or articles

🛠️ Future Enhancements

Save diffs to a database
Visualize changes over time
Monitor multiple pages in parallel
Email alerts as a fallback

✅ Final Thoughts

This simple automation stack — built with Python + Streamlit + Slack + Olostep — empowers you to stay ahead of competitors or monitor any site changes. Customize it to track any page that matters.

Happy hacking 🚀

Build an AI-Powered Stock Analyzer Using Streamlit, Olostep, and OpenAI

Mohammad Ehsan Ansari — Fri, 02 May 2025 05:52:47 +0000

Build an AI-Powered Stock Analyzer Using Streamlit, Olostep, and OpenAI

Ever wanted to analyze stocks using AI without manually going through dozens of websites?

In this tutorial, you’ll build a Streamlit app that:

Uses Olostep's scraping API to fetch stock data from MarketWatch
Sends the content to OpenAI GPT-4 to rate stocks based on their performance and description
Visualizes the investment score with interactive charts

Source Code

🛠️ Requirements

Make sure to install the following dependencies:

pip install streamlit openai requests python-dotenv matplotlib beautifulsoup4

Create a .env file and add:

OPENAI_API_KEY=your_openai_key
OLOSTEP_API_KEY=your_olostep_key

🔧 Step 1: Scrape Stock Pages with Olostep

Use Olostep’s scrape endpoint to fetch the latest data for stock tickers from MarketWatch.

def scrape_stock_page(ticker):
    headers = {
        "Authorization": f"Bearer {os.getenv('OLOSTEP_API_KEY')}",
        "Content-Type": "application/json"
    }
    payload = {
        "formats": ["markdown"],
        "country": "US",
        "url_to_scrape": f"https://www.marketwatch.com/investing/stock/{ticker}"
    }
    res = requests.post("https://api.olostep.com/v1/scrapes", headers=headers, json=payload)
    return res.json().get("result", {}).get("markdown_content", "")

🧠 Step 2: Analyze Stock Content with OpenAI GPT-4

We ask GPT-4 to evaluate and assign a score from 0 to 100 for each stock.

def analyze_stocks_with_gpt(tickers, markdowns):
    prompt = "You are a stock analyst. Based on the following markdowns from MarketWatch, rate each stock on a scale of 0–100 as an investment opportunity. Return only JSON format like:

"
    prompt += '''{
  "scores": [
    {"stock_name": "AAPL", "score": 92},
    {"stock_name": "MSFT", "score": 88}
  ]
}'''


    for ticker, md in zip(tickers, markdowns):
        prompt += f"Ticker: {ticker}
Content:
{md[:3000]}

"

    response = openai_client.chat.completions.create(
        model="gpt-4",
        messages=[{"role": "user", "content": prompt}],
    )

    return json.loads(response.choices[0].message.content.strip())

🎨 Step 3: Visualize Results in Streamlit

def plot_scores(scores):
    stock_names = [item['stock_name'] for item in scores]
    values = [item['score'] for item in scores]

    fig, ax = plt.subplots(figsize=(10, 4))
    ax.bar(stock_names, values, color="green")
    ax.set_ylabel("Investment Score")
    ax.set_title("AI-Scored Stocks by OpenAI GPT-4")
    st.pyplot(fig)

🚀 Step 4: Complete Streamlit App

st.set_page_config(page_title="AI Stock Analyzer", page_icon="📈")
st.title("📈 AI Stock Analyzer with Olostep + OpenAI")

tickers_input = st.text_input("Enter comma-separated stock tickers (e.g., AAPL,MSFT,TSLA)")

if st.button("Analyze"):
    tickers = [t.strip().lower() for t in tickers_input.split(",") if t.strip()]
    markdowns = [scrape_stock_page(ticker) for ticker in tickers]

    if not any(markdowns):
        st.error("No data found.")
    else:
        st.info("Analyzing stocks with GPT...")
        scores = analyze_stocks_with_gpt(tickers, markdowns)
        st.success("Analysis complete!")
        plot_scores(scores['scores'])

✅ Example Output

Stock	Score
AAPL	91
TSLA	84
MSFT	89

💼 Use Cases

Retail investors screening multiple stocks
Analysts comparing high-volume tickers
Developers building AI-based investing tools
Newsletter writers looking to automate stock ideas

🔮 What's Next

Export scores to a Google Sheet or PDF
Use historical stock data and financial ratios
Add GPT explanation for each score
Deploy on Streamlit Cloud

💡 Final Thoughts

This app combines real-time scraping, AI rating, and visual insights into one seamless experience. It’s ideal for anyone looking to use AI to make smarter investment decisions at scale.

Happy investing! 📈

Analyze GitHub Profiles Using Olostep API and GPT-4 in Streamlit

Mohammad Ehsan Ansari — Fri, 02 May 2025 03:45:13 +0000

Analyze GitHub Profiles Using Olostep API and GPT-4 in Streamlit

Want to extract meaningful insights from a developer’s GitHub profile automatically? In this guide, we’ll walk through how to build a Streamlit web app that:

Scrapes GitHub profile content using Olostep’s Scrape API
Uses OpenAI GPT-4 to analyze and summarize profile insights
Displays the results neatly using Streamlit

Objectives

Scrape public GitHub profiles using Olostep API
Generate analysis using OpenAI GPT-4
Build an intuitive UI with Streamlit
Present insights like skills, contributions, and collaboration

Requirements

pip install streamlit openai requests python-dotenv

Create a .env file in your root directory with your API credentials:

OLOSTEP_API_KEY=your_olostep_api_key
OPENAI_API_KEY=your_openai_api_key

Project Structure

github-analyzer/
├── app.py
├── .env
└── requirements.txt

Step 1: Import Libraries and Load Environment Variables

import os
import requests
import streamlit as st
from dotenv import load_dotenv
from openai import OpenAI

load_dotenv()
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
olostep_api_key = os.getenv("OLOSTEP_API_KEY")

Step 2: Scrape GitHub Profile using Olostep

def scrape_profile(username):
    headers = {
        "Authorization": f"Bearer {os.getenv('OLOSTEP_API_KEY')}",
        "Content-Type": "application/json"
    }
    payload = {
        "formats": ["markdown"],
        "country": "US",
        "url_to_scrape": f"https://github.com/{username}"
    }
    res = requests.post("https://api.olostep.com/v1/scrapes", headers=headers, json=payload)
    return res.json().get("result", {}).get("markdown_content", "")

Step 3: Analyze Markdown Content with GPT-4

def analyze_with_gpt(username, markdown):
    prompt = f"""
    Analyze the GitHub profile below for insights:
    - Professional background (company, location, role)
    - Activity (repos, stars, streaks)
    - Tech stack
    - Community engagement

    Username: {username}

    Markdown Content:
    {markdown}
    """
    response = client.chat.completions.create(
        model="gpt-4",
        messages=[{"role": "user", "content": prompt}],
    )
    return response.choices[0].message.content.strip()

Step 4: Build Streamlit UI

st.set_page_config(page_title="GitHub Analyzer", page_icon="🐙")
st.title("🐙 GitHub Profile Analyzer")
username = st.text_input("Enter a GitHub username (e.g., torvalds)")

if st.button("Analyze Profile"):
    with st.spinner("Scraping GitHub profile..."):
        md = scrape_profile(username)
    if not md:
        st.error("Could not scrape the profile.")
    else:
        with st.spinner("Generating AI analysis..."):
            report = analyze_with_gpt(username, md)
        st.markdown("## 🧠 GPT-4 Generated Insights")
        st.markdown(report)

Example Output

GitHub Profile Analysis: torvalds

## 1. Professional Background
• Location: Portland, Oregon
• Works on the Linux kernel

## 2. Activity Analysis
• Top repository: linux
• Thousands of contributions
• Maintains core system software

## 3. Tech Stack
• C, C++, Shell scripting

## 4. Community Engagement
• Collaborates with hundreds of devs
• Active in pull request reviews

Use Cases

Hiring teams evaluating developer contributions
Candidates generating portfolio summaries
Open-source communities reviewing contributors
AI agents assessing technical depth of profiles

Next Steps

Add export to PDF
Add batch processing for multiple usernames
Deploy to Streamlit Cloud

Final Thoughts

This full-stack project combines web scraping, AI summarization, and interactive frontend to deliver structured GitHub insights. With Olostep + OpenAI + Streamlit, you can automate what once required hours of manual review.

Happy hacking! 🧙‍♂️

How to Find and Extract All URLs from a Website Using Olostep Maps API and Streamlit

Mohammad Ehsan Ansari — Sun, 27 Apr 2025 12:05:09 +0000

Introduction

When building web crawlers, competitive analysis, SEO audits, or AI agents, one of the first critical tasks is finding all the URLs on a website.

While traditional methods like Google search tricks, sitemap exploration, and SEO tools work, there's a faster, modern way: using Olostep Maps API.

In this guide, we'll:

Introduce the challenge of URL discovery
Show how to build a live Streamlit app to scrape all URLs
Compare it with traditional techniques (like sitemap.xml and robots.txt)
Provide complete runnable Python code

Target Audience: Developers, Growth Engineers, Data Scientists, SEO specialists, and Founders who need structured, scalable scraping.

Why Extract All URLs?

Finding every page on a website can help you:

Analyze site structure (for SEO)
Scrape website content efficiently
Find hidden gems like orphan pages
Monitor website changes
Prepare data for AI agents and automation

Traditional Methods (Before Olostep)

1. Sitemaps (XML Files)

Webmasters often create XML sitemaps to help Google index their sites. Here's an example:

<urlset>
  <url>
    <loc>https://example.com</loc>
  </url>
  <url>
    <loc>https://example.com/about</loc>
  </url>
</urlset>

To find sitemaps:

Visit /sitemap.xml (e.g., https://example.com/sitemap.xml)
Check /robots.txt (it usually links to the sitemap)

Other possible sitemap locations:

/sitemap.xml.gz
/sitemap_index.xml
/sitemap.php

You can also Google:

site:example.com filetype:xml

Problems:

Some websites don't maintain updated sitemaps.
Not all pages may be listed.
Dynamic websites (heavy JavaScript) often leave out many pages.

2. Robots.txt

Example:

User-agent: *
Sitemap: https://example.com/sitemap.xml
Disallow: /admin

Good for finding disallowed URLs and sitemap links, but again not comprehensive.

The Modern Solution: Olostep Maps API

✅ Find up to 100,000 URLs in seconds.

✅ No need to manually find sitemap or robots.txt.

✅ Simple API call.

✅ No server maintenance or IP bans.

👉 Full code Gist

Let's build a full Streamlit app to demo this!

🛠️ Full Project: Website URL Extractor with Olostep Maps API + Streamlit

1. Install Requirements

pip install streamlit requests

2. Python Code

import streamlit as st
import requests
import json

def fetch_urls(target_url, api_key):
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json"
    }
    payload = {"url": target_url}
    response = requests.post("https://api.olostep.com/v1/maps", headers=headers, json=payload)
    if response.status_code == 200:
        return response.json()
    else:
        st.error(f"Failed to fetch URLs. Status code: {response.status_code}")
        return None

st.title("🔎 Website URL Scraper")

st.markdown("Use Olostep Maps API to instantly extract all discovered URLs from any website. Great for SEO, scraping, site analysis, and more!")

api_key = st.text_input("Enter your Olostep API Key", type="password")
url_to_scrape = st.text_input("Enter Website URL (e.g., https://example.com)")

if st.button("Find URLs"):
    if api_key and url_to_scrape:
        with st.spinner("Fetching URLs..."):
            data = fetch_urls(url_to_scrape, api_key)
        if data:
            urls = data.get("urls", [])
            st.success(f"✅ Found {len(urls)} URLs!")
            for idx, u in enumerate(urls, start=1):
                st.markdown(f"{idx}. [{u}]({u})")

            st.download_button(
                "📄 Download URLs as Text File",
                data="\n".join(urls),
                file_name="discovered_urls.txt",
                mime="text/plain"
            )

📸 Example Output

✅ Found 35 URLs from https://docs.olostep.com

📥 Saved as discovered_urls.txt

⚡ Why Olostep Maps API Beats Traditional Methods

Feature	Sitemap/Robots.txt	SEO Spider	Olostep Maps
Instant Response	❌	❌	✅
Handles JS-heavy Sites	❌	⚠️ (Partial)	✅
Handles Big Sites	❌	❌ (Limit)	✅
No Setup Needed	❌	❌	✅
Easy Pagination	❌	❌	✅

📈 Conclusion

Using Olostep Maps API + a few lines of Streamlit code, you can build powerful website discovery tools in minutes.

No more worrying about sitemaps, robots.txt, or getting blocked by firewalls.

✅ Super fast

✅ Reliable

✅ Perfect for Growth Engineering, SEO, Scraping, and Automation.

🚀 Ready to try?

Written by:

Mohammad Ehsan Ansari

Growth Engineer @ Olostep

Happy scraping! 🚀

Build a Website Knowledge Chatbot Using Streamlit, ChromaDB, Olostep, and OpenAI

Mohammad Ehsan Ansari — Fri, 25 Apr 2025 13:43:53 +0000

Have you ever wanted a smart AI assistant that understands your entire website and can answer questions like ChatGPT? In this tutorial, we’ll show you how to build it — without training your own LLM or managing any backend.

We’ll use:

✅ Olostep to crawl and extract website content

✅ ChromaDB to store and search content embeddings with metadata

✅ OpenAI (v1.7.6) for embeddings and GPT-4 summarization

✅ Streamlit to build a live chatbot UI

Perfect for product sites, documentation portals, and landing pages.

🔧 What You'll Need

pip install streamlit openai==1.7.6 chromadb requests

🧠 How It Works

Crawl website pages using Olostep’s API
Clean content and extract Markdown
Embed each page with OpenAI embeddings
Store everything in ChromaDB (including metadata)
Let users ask questions via Streamlit
Query top matches and summarize answers with GPT

🧩 Step-by-Step Implementation

1. Crawl Website

def start_crawl(url):
    payload = {
        "start_url": url,
        "include_urls": ["/**"],
        "max_pages": 10,
        "max_depth": 3
    }
    headers = {"Authorization": f"Bearer YOUR_OLOSTEP_API_KEY"}
    res = requests.post("https://api.olostep.com/v1/crawls", headers=headers, json=payload)
    return res.json()["id"]

2. Wait and Retrieve Pages

def wait_for_crawl(crawl_id):
    while True:
        res = requests.get(f"https://api.olostep.com/v1/crawls/{crawl_id}", headers={"Authorization": f"Bearer YOUR_OLOSTEP_API_KEY"})
        if res.json()["status"] == "completed":
            break
        time.sleep(30)

def get_pages(crawl_id):
    res = requests.get(f"https://api.olostep.com/v1/crawls/{crawl_id}/pages", headers={"Authorization": f"Bearer YOUR_OLOSTEP_API_KEY"})
    return res.json()["pages"]

3. Clean Markdown Content

import re

def clean_markdown(markdown):
    markdown = re.sub(r'#+ |\* |\> ', '', markdown)
    markdown = re.sub(r'\[(.*?)\]\(.*?\)', r'\1', markdown)
    markdown = re.sub(r'`|\*\*|_', '', markdown)
    markdown = re.sub(r'\n{2,}', '\n', markdown)
    return markdown.strip()

4. Initialize ChromaDB

import chromadb
from chromadb.utils import embedding_functions

chroma_client = chromadb.Client()
openai_embed_fn = embedding_functions.OpenAIEmbeddingFunction(
    api_key="YOUR_OPENAI_API_KEY",
    model_name="text-embedding-ada-002"
)

collection = chroma_client.get_or_create_collection(
    name="website-content",
    embedding_function=openai_embed_fn
)

5. Index Content

def retrieve_markdown(retrieve_id):
    res = requests.get("https://api.olostep.com/v1/retrieve",
        headers={"Authorization": f"Bearer YOUR_OLOSTEP_API_KEY"},
        params={"retrieve_id": retrieve_id, "formats": ["markdown"]})
    return res.json().get("markdown_content", "")

def index_content(pages):
    for page in pages:
        try:
            markdown = retrieve_markdown(page["retrieve_id"])
            text = clean_markdown(markdown)
            if len(text) > 20:
                collection.add(
                    documents=[text],
                    metadatas=[{"url": page["url"]}],
                    ids=[page["retrieve_id"]]
                )
                print(f"✅ Indexed: {page['url']}")
        except Exception as e:
            print(f"⚠️ Error: {e}")

6. Summarize with GPT

import openai
client = openai.OpenAI(api_key="YOUR_OPENAI_API_KEY")

def summarize_with_gpt(question, chunks):
    if not chunks:
        return "Sorry, I couldn't find enough information."

    prompt = f'''
Use the following website content to answer this question:

{''.join(chunks)}

Q: {question}
A:
'''

    res = client.chat.completions.create(
        model="gpt-4",
        messages=[{"role": "user", "content": prompt}],
        temperature=0.4,
    )
    return res.choices[0].message.content

7. Streamlit Frontend

import streamlit as st

st.set_page_config(page_title="Website Chatbot", page_icon="💬")
st.title("💬 Website Chatbot")
st.caption("Ask anything based on your website content.")

question = st.chat_input("Ask your question...")

if question:
    with st.chat_message("user"):
        st.markdown(question)

    with st.spinner("Thinking..."):
        chunks = query_website(question)
        final_answer = summarize_with_gpt(question, chunks)

    with st.chat_message("assistant"):
        st.markdown(final_answer)

✅ Live Demo Preview

Ask: What services do you offer?
Ask: Where is your pricing page?
Ask: How can I contact support?

The assistant will generate answers using real indexed content from your website.

🧠 Next Steps

Save/load your ChromaDB collection
Split large documents into smaller chunks
Include source URLs in GPT responses
Add memory to handle multi-turn chat

🎯 Conclusion

Congratulations! You've now built a fully functional AI-powered chatbot that can answer questions from your website using ChromaDB, Olostep, and OpenAI — all wrapped in a beautiful Streamlit app.

Whether for internal docs, support, or public knowledge, this gives you ChatGPT power without managing any LLMs.

Happy building! 🚀
https://gist.github.com/mdehsan873/f69481997f487e23b1d1282c82ce00f5