Exporting a Blank Excel Sheet

Question

I am trying to extract the following using Polygon.io.

I have a xcel sheet with Date (column 1) and Ticker (Column 2). I want the trading volume of the ticker for each day for the previous 30 days from the Date listed in Column 1 for the Ticker in column 2. For example, " 2018-07-09" is listed in column 1, "MBVX" is listed in column 2. I want the previous 30 days trading volume for MBVX from 2018-07-09. The output volume will be labeled day 1 volume, day 2 volumes and etc. This the code I am using but when I run the code and it exports the excel sheet there is nothing on it except for my two columns.

import pandas as pd
import requests
from datetime import timedelta

# Read the Excel sheet into a pandas DataFrame
df = pd.read_excel(r'C:\Users\patri\Downloads\Volume data.xlsx')

# Create a new DataFrame to store the volume data
volume_data_df = pd.DataFrame()

# Iterate over each row in the DataFrame
for index, row in df.iterrows():
date = row[0]
ticker = row[1]

# Define the start and end dates for the desired 30-day range
end_date = pd.to_datetime(date)
start_date = end_date - timedelta(days=30)

# Construct the API URL to retrieve historical stock data
api_url = f"https://api.polygon.io/v2/aggs/ticker/{ticker}/range/1/day/{start_date}/{end_date}?apiKey=My API KEY"

try:
    # Send a GET request to the Polygon API
    response = requests.get(api_url)

    if response.status_code == 200:
        # Extract the volume data from the API response
        data = response.json()
        if data['status'] == 'OK':
            results = data['results']
            for i, result in enumerate(results):
                volume_data_df.loc[index, f"Day {i+1} Volume"] = result['v']
        else:
            print(f"Error retrieving data for {ticker}: {data['error']}")
    else:
        print(f"Error retrieving data for {ticker}.")
except requests.exceptions.RequestException as e:
    print(f"Error making API request for {ticker}: {str(e)}")

# Combine the original DataFrame with the volume data
combined_df = pd.concat([df, volume_data_df], axis=1)

# Export the combined DataFrame to an Excel file
combined_df.to_excel('Volume Data2.xlsx', index=False)

It seems like no matter what I change, I get the same results. I have ran the code multiple times with slight variations and I get the same result every time. I am basically the definition of insanity right now.

My ideal excel sheet has my current columns Data, Ticker followed by 30 columns which each the volume for the previous day.

Answer 1

You missed converting data only string from pd.to_datetime(date)

This code will work.

import pandas as pd
import requests
from datetime import timedelta

MY_API_KEY = 'Your API Key'

combined_df = pd.DataFrame(columns=['Date', 'Symbol','Offset', 'Volume'])

# Read the Excel sheet into a pandas DataFrame
df = pd.read_excel(r'./Volume data.xlsx', usecols='A, B')
for index, row in df.iterrows():
    # get Stock Symbol
    ticker = row['Symbol']

    # Get Date and convert date only
    end_date = pd.to_datetime(row['Date'])
    start_date = end_date - timedelta(days = 30)
    start_date_format = "{:04d}-{:02d}-{:02d}".format(start_date.year, start_date.month, start_date.day)
    end_date_format = "{:04d}-{:02d}-{:02d}".format(end_date.year, end_date.month, end_date.day)

    # Construct the API URL to retrieve historical stock data
    api_url = f"https://api.polygon.io/v2/aggs/ticker/{ticker}/range/1/day/{start_date_format}/{end_date_format}?apiKey={MY_API_KEY}"

    try:
        # Send a GET request to the Polygon API
        response = requests.get(api_url)

        if response.status_code == 200:
            # Extract the volume data from the API response
            data = response.json()
            index = 1
            for day_data in data['results']:
                # date only
                trade_date = start_date + timedelta(days=index)
                trade_date_format = "{:04d}-{:02d}-{:02d}".format(trade_date.year, trade_date.month, trade_date.day)

                #Add row data
                offset_format = "Day {:02d} Volume".format(index)
                df2 = {'Date': trade_date_format, 'Symbol': ticker,'Offset': offset_format, 'Volume': day_data['v']}
                combined_df = pd.concat([combined_df, pd.DataFrame.from_records([df2])])
                index = index + 1
        else:
            print(f"Error retrieving data for {ticker}.")
    except requests.exceptions.RequestException as e:
        print(f"Error making API request for {ticker}: {str(e)}")

combined_df.to_excel('Volume Data2.xlsx', index = False)

Input Excel File

Output Excel File