How do i send Timestamp data to db using python?


I am trying to save data from yfinance to mongodb in a Timeseries db that I created following the instructions in the documentation. Is there an easy way of converting yfinance data to BSON UTC format? I need to write and read datetimes. I work with pymongo and pandas dataframes.

example timestamp:

Timestamp('2022-12-27 00:00:00-0500', tz='America/New_York')

thank you!

Timestamp(‘2021-12-28 00:00:00-0500’, tz=‘America/New_York’)

Use Python datetime.datetime() to instance the insertable value.

Hello @Jack_Woehr

Merry XMas. Could you please provide an example? I’m quite an aspiring dev and I dont fully understan what you mean.

In the field where you want a date, put the following in that field:

datetime.datetime(year, month, day, 0, 0, 0, 0)
assuming year month day are all instanced variables

the pymongo driver will correctly translate this during your insert() or insert_many()

@Yannis_Antypas I just remembered that a while ago I published a complete script that handles the date.
It converts a mariadb table to mongodb and does the date thing as part of that. Hope this helps.

1 Like

Hey @Jack_Woehr ,

thank you for your replies.

I wrote some code - its not the most efficient - but it works.

import pymongo
import yfinance as yf
import pandas as pd
from pymongo import MongoClient
import pytz
import datetime
from datetime import datetime, timezone

# Connect to the database
client = pymongo.MongoClient("mongodb://localhost:27017/")
db = client["stocks"]

# Get the "OHLCV" collection
ohlc_collection = db["OHLCV"]

# List of ticker symbols
ticker_symbols = ["AAPL","GOOG"]

# Create an empty list to store the data for each ticker
ticker_data_list = []

# Loop through the ticker symbols
for ticker in ticker_symbols:
        # Retrieve the stock data for the current ticker
        ticker_data = yf.Ticker(ticker).history(period="1y")
        # Add a column with the ticker symbol to the data
        ticker_data.insert(0, "Ticker", ticker)
        # Append the data to the list
    except Exception as e:
        print(f"An error occurred while retrieving data for {ticker}: {e}")

# Concatenate the data for each ticker into a single DataFrame
df = pd.concat(ticker_data_list).reset_index()

# Insert the 'timestamp' column in the first position
df.insert(0, 'timestamp', None)

# Iterate over the rows of the DataFrame
for index, row in df.iterrows():
    # Parse the timestamp string
    timestamp = row['Date']
    timestamp_str = str(timestamp)
    dt = datetime.strptime(timestamp_str, '%Y-%m-%d %H:%M:%S%z')

    # Convert the timestamp to UTC
    dt_utc = dt.astimezone(timezone.utc)

    # Use the strftime method to format the datetime object as a string in the desired format
    formatted_string = dt_utc.strftime("%Y-%m-%dT%H:%M:%SZ")
    dt = datetime.strptime(formatted_string, '%Y-%m-%dT%H:%M:%SZ')

    # Store the formatted string in the new column[index, 'timestamp'] = dt
# Drop the 'Date' column
df = df.drop(columns=['Date'])

# Get the data as a list of dictionaries
data_dict = df.to_dict(orient="records")

    # Insert the data into the collection
    result = ohlc_collection.insert_many(data_dict,
    # Print a message indicating the number of documents inserted and the current time
    print(f"Inserted {len(result.inserted_ids)} documents into the collection")
    print(f"last modified {datetime.utcnow()}")
except Exception as e:
    print(f"An error occurred while inserting data into the collection: {e}")
    # Close the connection to the database
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.