Trying to write stock data to line protocol with date/time and timezone support

,
1

can you please tell me what am I doing wrong here?
I’ve been banging my head on the wall with this

import yfinance as yf
import pandas as pd
import influxdb_client
import datetime
from datetime import datetime, date
from influxdb_client.client.write_api import PointSettings

token = "my-token"
org = "my-org"
url = "my-url"

client = influxdb_client.InfluxDBClient(url=url, token=token, org=org)

# Set the bucket and retention policy for the writes
bucket = "stocks_us"
retention_policy = "autogen"

# List of tickers to download data for
tickers = ["MSFT", 'GOOG']

# Create a dictionary to store the Line Protocol strings for each ticker
line_protocol_strings = {}

# Download the daily stock data for each ticker and store in the DataFrame
for ticker in tickers:
    stock_data = yf.Ticker(ticker).history(period="1d")
    stock_data = stock_data.reset_index()

    # Create an empty list to store the Line Protocol strings for this ticker
    line_protocol_strings[ticker] = []

    # Iterate over the rows of the DataFrame
    for _, row in stock_data.iterrows():
        
        # Extract the date, open, high, low, close, volume, and dividends values from the row
        # Convert the date to a string
        date = row['Date'].strftime('%Y-%m-%d')
        open = row['Open']
        high = row['High']
        low = row['Low']
        close = row['Close']
        volume = row['Volume']
        dividends = row['Dividends']
    
        # Convert the date to a Line Protocol timestamp
        dt = datetime.strptime(date, '%Y-%m-%d')
        unix_timestamp = dt.timestamp()
        line_protocol_timestamp = int(unix_timestamp * 1_000_000_000)
    
        # Create the Line Protocol string
        line_protocol_string = ''
        line_protocol_string += 'stock_prices '
        line_protocol_string += f'stock={ticker} '
        line_protocol_string += f'Open={open},High={high},Low={low},Close={close},Volume={volume},Dividends={dividends} '
        line_protocol_string += str(line_protocol_timestamp)

        # Add the Line Protocol string to the list for this ticker
        line_protocol_strings[ticker].append(line_protocol_string)

# Write the data to InfluxDB
for ticker, lines in line_protocol_strings.items():
    client.write_data(lines, bucket=bucket, retention_policy=retention_policy)

# Close the InfluxDB client
client.close()
2

I was able to write data but I am not able to correct the _time from the time stamp provided from yfinance. How do I write to db with timezone support? Please see modified code from the example provided in the docs:

ticker= 'AAPL'

df = yf.Ticker('AAPL').history(period="1mo")#.reset_index()


with client:
    """
    Ingest DataFrame with default tags
    """
    point_settings = PointSettings(**{"type": ticker})
    #point_settings.add_default_tag("example-name", "ingest-data-frame")

    write_api = client.write_api(write_options=SYNCHRONOUS, 
                                 point_settings=point_settings)
    write_api.write(bucket=bucket, 
                    org= "dev", 
                    record=df, 
                    data_frame_measurement_name="stock_daily_df")

client.close()

print(df)

the data from yfinance is Timestamp:

ticker= 'AAPL'
import yfinance as yf
df = yf.Ticker('AAPL').history(period="1mo").index[0]
df

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

I want to write the time data in UTC.
How can I do this?

data written in db:
Screenshot 2023-01-05 at 2.27.50 PM