Daily Share Price Notifications using Python, SQL and Africas Talking - Part One
Tags: python, tutorial, postgres, sql
Published: 2021-05-02 11:16:37

On a hot dry afternoon we sat discussing investment avenues during this pandemic, we realized they are limited. However, we kept coming back to shares as a solid medium to explore. This presented a good project idea as well as a good opportunity to learn more about workings of the financial sector. Here in Kenya, the main bourse is the Nairobi Stock Exchange(NSE), there are about 60+ companies listed on the exchange as of 2021. The NSE operates from Monday from Friday from 9.00 am to 3.00 pm except holidays. The main aim of this article is to develop a web scraper, and a notification script to notify us when certain ticker reaches a specific price or alternatively above a certain price threshold.

Having done web scraping projects before, I have researched an extensive list of libraries and frameworks and other tools. You can check out my write-up on a [news scraper](https://dev.to/ken_mwaura1/daily-news-scraper-and-sms-notifications-part-one-15m9). I had a little experience with using [scrapy](https://docs.scrapy.org/en/latest/) and seemed like the perfect fit for this project. Scrapy is a web scraping framework thus it makes assumptions on how to handle certain aspects ranging from folder structure to its own cli and storing data.

This makes it great for structuring large projects or even multiple scrapers in one project. However, it also has a steep learning curve but the in-depth documentation and fairly large community more than makes up for it. For storing data usually a JSON file would be adequate, but a database ensures it'll be easy to persist and query data later on. We'll be making use of Postgresql, mainly because I used already in other [projects](https://dev.to/ken_mwaura1/automate-customer-rewards-using-python-postgresql-and-africas-talking-9dj) and serves our needs nicely.

## Prerequisites before getting started
To follow along this post and code the same features. You're going to need a few things:
- Python and pip (I am currently using 3.9.2) Any version above 3.5 should work.
- An [Africas Talking account](https://account.africastalking.com/auth/register/).
    - Api Key and username from your account. Create an app and take note of the api key.
      Once you've got the above sorted :
        - Create a new directory and change into it.
             ```shell
    
                     mkdir nse_scraper
                     cd nse_scraper 
       
            ```
    - Create a new virtual environment for the project or activate the previous one.
    - Using python package manager(pip), install: beautifulsoup4, scrapy, africastalking python sdk, python-dotenv library,
      sqlachemy and psycopg2 libraries.
    - Save the installed libraries in a requirements.txt file

      ```python 
      
           python -m venv .
           source bin/activate
           pip install africastalking beautifulsoup4 scrapy python-dotenv sqlachemy psycopg2  
           pip freeze > requirements.txt
      
      ```

As mentioned above we are using Postgresql as our database of choice hence we need a library to interface with the database, psycopg2 is a good option although there are
others. Although not necessary we'll be making use of   [SqlAlchemy](https://www.sqlalchemy.org/) as our Object Relation Mapper(ORM). This allows us to use python objects (classes, functions) to make transactions instead of raw SQL.
- Install Postgresql database to save all of our scraped data. Depending on which platform you code on, you could do it natively on your system. Personally I am using [docker](https://www.docker.com/) as it is easy to manage containers and prevents my system from being cluttered.
  This [article](https://blog.crunchydata.com/blog/easy-postgresql-12-and-pgadmin-4-setup-with-docker) is an awesome resource on how to get Postgresql and pgadmin4  installed as containers.

Alternatively, check the finished code on [Github](https://github.com/KenMwaura1/stock-price-scraper)

## Spiders Everywhere 🕷️🕸️
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tto9wulhv52hodxl5h5k.jpg)
Scrapy operates on the concept of spiders, we define our own custom spiders to crawl and scrape data.
Scrapy has its commands that makes creating a project and a spider(s) quick and easy.
Now we will create a scrapy project, generate a spider with the required boilerplate code using the cli.
```python
scrapy startproject nse_scraper 
```
Running the startproject command will create a folder with the structure outlined below. There is a top folder with the project name (nse_scraper) that contains the Scrapy configuration and a subfolder with the same name containing the actual crawling code.
```shell
python-projects $ tree nse_scraper
nse_scraper
├── nse_scraper
│   ├── __init__.py
│   ├── items.py
│   ├── middlewares.py
│   ├── pipelines.py
│   ├── settings.py
│   └── spiders
│       └── __init__.py
└── scrapy.cfg

2 directories, 7 files
```
NB: I don't want to go into too much detail about Scrapy because there are many tutorials for the tool online, and because I normally use requests with lxml to make (very simple) data crawlers.
Many people prefer to use BeautifulSoup or other higher level data crawl libraries so feel free to go for that.
I picked Scrapy in this particular case because it creates a nice scaffold when working with crawlers and databases but this can be completely done from scratch as well.
```python
cd nse_scraper 
scrapy genspider afx_scraper https://afx.kwayisi.org/nseke/
Created spider 'afx_scraper' using template 'basic' in module:
nse_scraper.spiders.afx_scraper
```
You could choose to not use the generator and write the Scrapy files yourself but for simplicity I use the boilerplate that comes with Scrapy.
Now navigate to the top level project folder and create the spider (*afx_scraper*) using `genspider`. In my case I will be crawling data from [afx](afx.kwayisi.org} about NSE share prices. There is the main [nse website](https://www.nse.co.ke/)
or even [mystocks website](http://mystocks.co.ke/), however both require a subscription to get real time stock quotes. Since this project is meant to be a DIY scraper with minimal costs, afx was the most viable option. As a bonus they structure their data in a table and regularly update the prices.
As seen below: 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f6zec5vpxv1xu027vent.png)

If we take a look at the file structure again inside the *spiders* folder, a new file *afx_scraper.py* has been created.
```shell
python-projects/nse_scraper $ tree
.
├── nse_scraper 
│   ├── __init__.py
│   ├── items.py
│   ├── middlewares.py
│   ├── pipelines.py
│   ├── settings.py
│   └── spiders
│       ├── __init__.py
│       └── afx_scraper.py 
└── scrapy.cfg
```
The content of `afx_scraper.py` is the minimum code required to get started with crawling data.
```python
#afx_scraper.py 
import scrapy
class AfxScraperSpider(scrapy.Spider):
    name = 'afx_scraper'
    allowed_domains = ['https://afx.kwayisi.org']
    start_urls = ['https://afx.kwayisi.org/nseke/']
    
    def parse(self, response):
        pass
```
## Scraper Setup
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c2r4vd68ksv7utdpv30f.jpg)
The first element we want to crawl is the table element holding all the data, we then loop through and get each ticker symbol, share name and price. The code to get data is added to the `parse` function. Looking through the developer tools inside our browser we see that `table` element has a `tbody` element that holds `tr` elements. This refers to table row html element, each row contains `td` elements. This refers to table data element. this is element we want to scrape.

Scrapy allows for two ways of selecting elements in a html document:
1. Using CSS selectors
2. Using Xpath.

We'll start off with using CSS selector as its straightforward. We assign a `row` variable to the code referencing the row of data. Due to the nature of how the individual data is displayed (similar html tags) we need to use xpath to extract data.
```python
#afx_scraper.py
 def parse(self, response):
        print("Processing: " + response.url)
        # Extract data using css selectors
        row = response.css('table tbody tr ')
        # use XPath and regular expressions to extract stock name and price
        raw_ticker_symbol = row.xpath('td[1]').re('[A-Z].*')
        raw_stock_name = row.xpath('td[2]').re('[A-Z].*')
        raw_stock_price = row.xpath('td[4]').re('[0-9].*')

        # create a function to remove html tags from the returned list
        print(raw_ticker_symbol)
```
For each row above we use xpath to extract the required elements. The result is a combined list of data including data from the top table including top gainers and losers. Inorder to filter out what we dont need, we use [regular expressions](https://docs.python.org/3/library/re.html). In the case of `raw_ticker_symbol` and `raw_stock_price` we only need alphabetic letters thus we pass along `[A-Z].*` rules to our regex. As for our price data, we need integers we pass `[0-9].*` as our regex rule.

#### Creepy Crawlers 🐜
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/21z8zl4hm2ygqtxzl6ih.png)
Now the scraper is ready to be executed and retrieve the items. Run the crawler and verify that it is returning indeed the items that you would expect. There is no output that stores the items yet but the log tells me that there were 66 items that actually had a symbol, name and the price defined ('item_scraped_count': 66,). Note that I set the loglevel to INFO to prevent an information overload in the console.
```shell
2021-04-30 23:02:09 [scrapy.utils.log] INFO: Scrapy 2.4.1 started (bot: nse_scraper)
2021-04-30 23:02:09 [scrapy.utils.log] INFO: Versions: lxml 4.6.3.0, libxml2 2.9.10, cssselect 1.1.0, parsel 1.6.0, w3lib 1.22.0, Twisted 21.2.0, Python 3.9.4 (default, Apr 20 2021, 15:51:38) - [GCC 10.2.0], pyOpenSSL 20.0.1 (OpenSSL 1.1.1k  25 Mar 2021), cryptography 3.4.7, Platform Linux-5.11.14-147-tkg-bmq-x86_64-with-glibc2.33
2021-04-30 23:02:09 [scrapy.crawler] INFO: Overridden settings:
{'BOT_NAME': 'nse_scraper',
 'EDITOR': '/usr/bin/micro',
 'LOG_LEVEL': 'INFO',
 'NEWSPIDER_MODULE': 'nse_scraper.spiders',
 'SPIDER_MODULES': ['nse_scraper.spiders']}
2021-04-30 23:02:09 [scrapy.extensions.telnet] INFO: Telnet Password: 616b228b56a699b0
2021-04-30 23:02:09 [scrapy.middleware] INFO: Enabled extensions:
['scrapy.extensions.corestats.CoreStats',
 'scrapy.extensions.telnet.TelnetConsole',
 'scrapy.extensions.memusage.MemoryUsage',
 'scrapy.extensions.feedexport.FeedExporter',
 'scrapy.extensions.logstats.LogStats']
2021-04-30 23:02:09 [scrapy.middleware] INFO: Enabled downloader middlewares:
['scrapy.downloadermiddlewares.httpauth.HttpAuthMiddleware',
 'scrapy.downloadermiddlewares.downloadtimeout.DownloadTimeoutMiddleware',
 'scrapy.downloadermiddlewares.defaultheaders.DefaultHeadersMiddleware',
 'scrapy.downloadermiddlewares.useragent.UserAgentMiddleware',
 'scrapy.downloadermiddlewares.retry.RetryMiddleware',
 'scrapy.downloadermiddlewares.redirect.MetaRefreshMiddleware',
 'scrapy.downloadermiddlewares.httpcompression.HttpCompressionMiddleware',
 'scrapy.downloadermiddlewares.redirect.RedirectMiddleware',
 'scrapy.downloadermiddlewares.cookies.CookiesMiddleware',
 'scrapy.downloadermiddlewares.httpproxy.HttpProxyMiddleware',
 'scrapy.downloadermiddlewares.stats.DownloaderStats']
2021-04-30 23:02:09 [scrapy.middleware] INFO: Enabled spider middlewares:
['scrapy.spidermiddlewares.httperror.HttpErrorMiddleware',
 'scrapy.spidermiddlewares.offsite.OffsiteMiddleware',
 'scrapy.spidermiddlewares.referer.RefererMiddleware',
 'scrapy.spidermiddlewares.urllength.UrlLengthMiddleware',
 'scrapy.spidermiddlewares.depth.DepthMiddleware']
2021-04-30 23:02:09 [scrapy.middleware] INFO: Enabled item pipelines:
['nse_scraper.pipelines.NseScraperPipeline']
2021-04-30 23:02:09 [scrapy.core.engine] INFO: Spider opened
2021-04-30 23:02:09 [scrapy.extensions.logstats] INFO: Crawled 0 pages (at 0 pages/min), scraped 0 items (at 0 items/min)
2021-04-30 23:02:09 [py.warnings] WARNING: /home/zoo/.pyenv/versions/stock-price-scraper/lib/python3.9/site-packages/scrapy/spidermiddlewares/offsite.py:65: URLWarning: allowed_domains accepts only domains, not URLs. Ignoring URL entry https://afx.kwayisi.org in allowed_domains.
  warnings.warn(message, URLWarning)

2021-04-30 23:02:09 [scrapy.extensions.telnet] INFO: Telnet console listening on 127.0.0.1:6023
Processing: https://afx.kwayisi.org/nseke/
2021-04-30 23:02:17 [scrapy.core.engine] INFO: Closing spider (finished)
2021-04-30 23:02:17 [scrapy.extensions.feedexport] INFO: Stored json feed (66 items) in: test.json
2021-04-30 23:02:17 [scrapy.statscollectors] INFO: Dumping Scrapy stats:
{'downloader/request_bytes': 443,
 'downloader/request_count': 2,
 'downloader/request_method_count/GET': 2,
 'downloader/response_bytes': 9754,
 'downloader/response_count': 2,
 'downloader/response_status_count/200': 1,
 'downloader/response_status_count/301': 1,
 'elapsed_time_seconds': 7.457298,
 'finish_reason': 'finished',
 'finish_time': datetime.datetime(2021, 4, 30, 20, 2, 17, 77130),
 'item_scraped_count': 66,
 'log_count/INFO': 11,
 'log_count/WARNING': 1,
 'memusage/max': 75550720,
 'memusage/startup': 75550720,
 'response_received_count': 1,
 'scheduler/dequeued': 2,
 'scheduler/dequeued/memory': 2,
 'scheduler/enqueued': 2,
 'scheduler/enqueued/memory': 2,
 'start_time': datetime.datetime(2021, 4, 30, 20, 2, 9, 619832)}
2021-04-30 23:02:17 [scrapy.core.engine] INFO: Spider closed (finished)

```

### Lets Clean the data!
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3z4ovlkinm04dkrv59gy.jpg)
The data we get is not usable in its current format as it contains html tags, classes,
attributes etc. Thus we need to clean it.
```python
#afx_scraper.py
#import BeautifulSoup at the top of the file
from bs4 import BeautifulSoup

# create a function to remove html tags from the returned list 
def clean_stock_name(raw_name):
            clean_name = BeautifulSoup(raw_name, "lxml").text
            clean_name = clean_name.split('>')
            return clean_name[1]

def clean_stock_price(raw_price):
    clean_price = BeautifulSoup(raw_price, "lxml").text
    return clean_price
    
 # Use list comprehension to unpack required values 
stock_name = [clean_stock_name(r_name) for r_name in raw_stock_name]
stock_price = [clean_stock_price(r_price) for r_price in raw_stock_price]
stock_symbol = [clean_stock_name(r_symbol) for r_symbol in raw_ticker_symbol]
# using list slicing to remove the unnecessary data
stock_symbol = stock_symbol[6:]
cleaned_data = zip(stock_symbol, stock_name, stock_price)
for item in cleaned_data:
    scraped_data = {
        'ticker': item[0],
        'name': item[1],
        'price': item[2],
    }
    # yield info to scrapy
    yield scraped_data
```
We first import BeautifulSoup library from the bs4 package. This will give us an easier time cleaning the data. The first function `clean_stock_name()` accepts a value `raw_name`, we then call the BeautifulSoup constructor, pass our value as
an argument, we then specify *lxml* as our parser. For further details on how Beautiful Soup works and different parsers, [check out the documentation](https://www.crummy.com/software/BeautifulSoup/bs4/doc/#installing-a-parser). We then specify we want only the text and assign it to our `clean_name` variable. While cleaning the name, we still had additional characters that we didn't need, thus we call the `.split()` method and return the required string.

The second function `clean_stock_name()` pretty much repeats the process outlined above with the only adjustment is we don't need the extra step of adding the string split method.

We then call the functions on the each value of `raw_ticker_symbol`, `raw_name` and `raw_stock_price`. We proceed to assign the result to appropriately named variables: `stock_symbol`, `stock_price` and `stock_name`. The stock symbol returns additional characters than we need hence we do list slicing to get the correct length of characters and assign it to the variable.  We use the zip function to create a list of all of the data retrieved. Finally we create a dictionary `scraped_data` and assign relevant keys to the value of cleaned data. By using the `yield` keyword our parse function is now generator thus able to return values when needed. This is especially
critical to performance when crawling multiple pages.

## Lets Store all the Data!
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a4suqqsobc3b6vrhoa35.jpg)
First of all I define the schema of the element that I am crawling in the *items.py.*
There is no fancy schema yet but this can obviously be improved in the future when more items are being retrieved and the actual datatypes do make a difference.
```python
# items.py
# See documentation in:
# https://docs.scrapy.org/en/latest/topics/items.html


from scrapy.item import Item, Field


class NseScraperItem(Item):
    # define the fields for your item here like:
    stock_name = Field()
    stock_price = Field()
    stock_symbol = Field()
```

The *middlewares.py* is left untouched for the project. The important bit for storing data in a database is inside models.py. As described before I use SQLAlchemy to connect to the PostgreSQL database. The database details are stored in *settings.py* (see below) and are used to create the SQLAlchemy engine. I define the Items model with the three fields and use the create_items_table to create the table.
```python
# nse_scraper/nse_scraper/models.py
from sqlalchemy import Column, Float, Integer, String, create_engine
from sqlalchemy.engine.base import Engine
from scrapy.utils.project import get_project_settings
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


def db_connect() -> Engine:
    """
    Creates database connection using database settings from settings.py.
    Returns sqlalchemy engine instance
    """
    return create_engine(get_project_settings().get("DATABASE"))


def create_items_table(engine: Engine):
    """
    Create the Items table
    """
    Base.metadata.create_all(engine)


class StockData(Base):
    """
    Defines the items model
    """

    __tablename__ = "stock_data"

    id = Column("id", Integer, primary_key=True, autoincrement=True)
    stock_ticker = Column("stock_ticker", String)
    stock_name = Column("stock_name", String)
    stock_price = Column("stock_price", Float)
```
Inside the *pipelines.py* the spider is connected to the database. When the pipeline is started it will initalize the database and create the engine, create the table and setup a SQLAlchemy session. The `process_item` function is part of the default code and is executed for every yielded item in the scraper. In this case it means it will be triggered every time a stock is retrieved with a ticker, name and price. Remember to always commit() when adding (or removing) items to the table.
```python
# nse_scraper/nse_scraper/pipelines.py
# Define your item pipelines here

# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from sqlalchemy.orm import sessionmaker

from nse_scraper.models import StockData, create_items_table, db_connect


class NseScraperPipeline:
    def __init__(self):
        """
        Initializes database connection and sessionmaker.
        Creates stock_data table
        """
        engine = db_connect()
        create_items_table(engine)
        self.Session = sessionmaker(bind=engine)

    def process_item(self, item, spider):
        """
        process item and store to database
        """
        session = self.Session()
        stock_data = StockData()
        stock_data.stock_name = item["name"]
        stock_data.stock_price = float(item["price"].replace(',', ''))
        stock_data.stock_ticker = item["ticker"]
        try:
            session.add(stock_data)
            session.commit()
            # query again
            obj = session.query(StockData).first()
            # print(obj.stock_ticker)
        except Exception as e:
            session.rollback()
            print(f"we have a problem, houston {e}")
            raise
        finally:
            session.close()
        return item
```
Finally, the *settings.py* is short and contains the information for the crawler.
The only items I have added are the DATABASE and LOG_LEVEL variables. You could choose to add your security details in this file but I would recommend to keep them secret and store them elsewhere. I have used a `.env` file to store my credentials then used the `python-dotenv` library to retrieve them. Note: The `.env` should be in the same folder as the *settings.py* file or specify file path in the brackets.
```python
# nse_scraper/nse_scraper/settings.py 
# Scrapy settings for nse_scraper project

# For simplicity, this file contains only settings considered important or
# commonly used. You can find more settings consulting the documentation:
#
#     https://docs.scrapy.org/en/latest/topics/settings.html
#     https://docs.scrapy.org/en/latest/topics/downloader-middleware.html
#     https://docs.scrapy.org/en/latest/topics/spider-middleware.html

import os
from dotenv import load_dotenv

load_dotenv()
BOT_NAME = 'nse_scraper'

SPIDER_MODULES = ['nse_scraper.spiders']
NEWSPIDER_MODULE = 'nse_scraper.spiders'

# POSTGRES SETTINGS
host = os.getenv("POSTGRES_HOST")
port = os.getenv("POSTGRES_PORT")
username = os.getenv("POSTGRES_USER")
password = os.getenv("POSTGRES_PASS")
database = os.getenv("POSTGRES_DB")
drivername = "postgresql"
DATABASE = f"{drivername}://{username}:{password}@{host}:{port}/{database}"

# Configure item pipelines
# See https://docs.scrapy.org/en/latest/topics/item-pipeline.html
ITEM_PIPELINES = {
    'nse_scraper.pipelines.NseScraperPipeline': 300,
}
LOG_LEVEL = "INFO"
# Crawl responsibly by identifying yourself (and your website) on the user-agent
# USER_AGENT = 'nse_scraper (+http://www.yourdomain.com)'

# Obey robots.txt rules
ROBOTSTXT_OBEY = False

```

Your scraper is now ready to run it:
```python 
scrapy crawl afx_scraper 
```
You should now see stock data in your database. Optionally you could output to a json file to quickly preview the data retrieved.
```python 
scrapy crawl afx_scraper -o stock.json
```

This article was originally meant to cover setup, data scraping and notification, however its already long and its easier break it down to two parts. Part two will cover: Database queries, sms notification using africas talking, deployment and scheduling of the web scraper. 

If you have any question or comments. Let me know in the comments,
or on [Twitter](https://twitter.com/Ken_Mwaura1).