Store Financial Data into a MongoDB Database

In my latest posts, we have been building different Python for Financial analysis tools. During this article, we are going to see how to store financial data into a MongoDB database . We will be creating a MongoDB database in Python and adding historical stock prices for Apple, Facebook and Google to it.

Store Financial Data into a MongoDB Database
Photo by panumas nikhomkhai from Pexels

Starting with MongoDB

MongoDB is a noSQL database which it is easily integrated with Python. It has a free version offering 500MB of free storage capacity.

You can create a free MongoDB Atlas account here. Then configure a free cluster, I have used AWS and selected the closer available region.

Store Financial Data into a MongoDB Database
Store Financial Data into a MongoDB Database

Once you have the free cluster created, create a username and a password and add your IP to the whitelist. It is very easy to do, just follow the get started tips provided by Mongo DB.

Now, we are ready to connect to our new cluster containing the databases. We only need to get access to the connection string. Within the Clusters menu, click connect:

Then, to connect to cluster select the second option, Connect your application. Next, select Python as driver and your Python version. Finally, select the Full Driver Example tab and copy the whole connection string.

The string looks similar to below. Note that we have to replace <password> by our real password.

#connection string
client = pymongo.MongoClient("mongodb://pythonforfinance:[email protected]:27017,cluster0-shard-00-01-apl4w.mongodb.net:27017,cluster0-shard-00-02-apl4w.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=admin&retryWrites=true&w=majority")

Connecting to MongoDB from Python

Let’s move to Python to start a connection to our database. It is super easy, we just need to install pymongo which is a Python package that makes super easy to write and read our database data:

#installing pymongo
pip install pymongo

Then, once pymongo is installed, we can import it as any other package in our Python script using import pymongo.

Retrieving Historical Stock Prices with Python

Now that we have our MongoDB setup, we can retrieve financial data to store in our database. For that, we will use a free API, financialmodelingprep, to retrieve historical prices from Apple, Facebook and Google.

import pymongo
import json
import requests

#retrieving historical prices from Apple, Google and Facebook.
historical_prices = requests.get('https://financialmodelingprep.com/api/v3/historical-price-full/AAPL,GOOG,FB')

#Convert response to json format
historical_prices = historical_prices.json()

print(historical_prices)
#outcome
{'historicalStockList': [{'symbol': 'GOOG',
   'historical': [{'date': '2004-08-19', 'close': 49.98},
    {'date': '2004-08-20', 'close': 53.95},
    {'date': '2004-08-23', 'close': 54.5},
    {'date': '2004-08-24', 'close': 52.24},
    {'date': '2004-08-25', 'close': 52.8},
    {'date': '2004-08-26', 'close': 53.75},
    {'date': '2004-08-27', 'close': 52.88},
    {'date': '2004-08-30', 'close': 50.81},
    {'date': '2004-08-31', 'close': 50.99},
....

We have now daily historical prices since 2004 for Google, Apple and Facebook within the historicalStockList dictionary. Therefore, we can parse now the response to have access to the list of dictionaries containing the historical prices for the three companies:

historical_prices = historical_prices['historicalStockList']

Setting up MongoDB Connection

Now that we have a dictionary containing historical prices for Apple, Facebook and Google, we can start a connection with our MongoDB database.

First of all we need to define the client string. Then, we store the client connection to a variable db.

Creating our first MongoDB Database

To create our first database, we can simply use client[‘historical_price’]. Just like this, we create a new database named historical prices.

Creating our first MongoDB Collection

Having created our first database, we need to create a collection to store the stock prices. Think of collections as standard database tables to store records.

Within square brakets, we pass the name of the collection to be created. Our collection name will be prices.

client = pymongo.MongoClient("mongodb://pythonforfinance:[email protected]:27017,cluster0-shard-00-01-apl4w.mongodb.net:27017,cluster0-shard-00-02-apl4w.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=admin&retryWrites=true&w=majority")

#connect to  database. It will create database call historical_price
db = client['historical_price']

#it will create a collection call tickers within the databasetickers. 
collection_name = 'prices'
db_cm = db[collection_name]

#load the historical prices to the prices collection. 
db_cm.insert_many(historical_prices)

Finally, we can insert the historical prices into our database by using insert_many method. Remember that historical prices contains a dictionary with all our stock prices.

Db_cm is simply letting MongoDB know in which database and collection we want to store the data. Similar result would be obtained using below code:

client['historical_price']['prices'].insert_many(historical_prices)

Retrieving Stock Prices from MongoDB

Now that we have the stock prices stored in our database. We can simply use standard pymongo methods to retrieve data. For example, we can retrieve all records stored in our database by using the method find():

for doc in db_cm.find():
    print(doc)
#outcome
{'_id': ObjectId('5e760ba6f5255faf8086dddc'), 'symbol': 'GOOG', 'historical': [{'date': '2004-08-19', 'close': 49.98}, {'date': '2004-08-20', 'close': 53.95}, {'date': '2004-08-23', 'close': 54.5}, {'date': '2004-08-24', 'close': 52.24}, {'date': '2004-08-25', 'close': 52.8}, {'date': '2004-08-26', 'close': 53.75}, {'date': '2004-08-27', 'close': 52.88}, {'date': '2004-08-30', 'close': 50.81}, {'date': '2004-08-31', 'close': 50.99}, {'date': '2004-09-01', 'close': 49.9....

Alternatively, we can also make queries to retrieve only Google stock prices. Check out the MongoDB query documentation for more details on how to query the collection.

query = {'symbol':'GOOG'  }
selection = db['prices'].find(query)
for doc in selection:
    print(doc)

#outcome
{'_id': ObjectId('5e760ba6f5255faf8086dddc'), 'symbol': 'GOOG', 'historical': [{'date': '2004-08-19', 'close': 49.98}, {'date': '2004-08-20', 'close': 53.95}, {'date': '2004-08-23', 'close': 54.5}, {'date': '2004-08-24', 'close': 52.24}, {'date': '2004-08-25', 'close': 52.8}, {'date': '2004-08-26', 'close': 53.75}, {'date': '2004-08-27', 'close': 52.88}, {'date': '2004-08-30', 'close': 50.81}, {'date': '2004-08-31', 'close': 50.99}, {'date': '2004-09-01', 'close': 49.9....

Wrapping Up

Great, we have retrieve from an API historical stock prices for Apple, Facebook and Google. Then, we have created a MongoDB connection and store all historical stock prices to a MongoDB collection.

See below code for your reference.

 Happy to continue the discussion in my Twitter account.

Check out my other posts to learn more Python for Finance.

import pymongo
import json
import pandas as pd
import requests

#retrieving historical prices from Apple, Google and Facebook.
historical_prices = requests.get('https://financialmodelingprep.com/api/v3/historical-price-full/AAPL,GOOG,FB')
#Convert response to json format
historical_prices = historical_prices.json()
historical_prices = historical_prices['historicalStockList']


client = pymongo.MongoClient("mongodb://pythonforfinance:[email protected]:27017,cluster0-shard-00-01-apl4w.mongodb.net:27017,cluster0-shard-00-02-apl4w.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=admin&retryWrites=true&w=majority")


#connect to  database. It will create database call historical_price
db = client['historical_price']
#it will create a collection call tickers within the databasetickers. Collection is like a database table
collection_name = 'prices'
db_cm = db[collection_name]

#load the historical prices to the prices collection. 

client['historical_price']['prices'].insert_many(historical_prices)

query = {'symbol':'GOOG'  }
selection = db['prices'].find(query)
for doc in selection:
    print(doc)