I’m working in a script for a week now with ChatGPT but I didn’t get the result I hope for
the output I want is in every dict the number of shares keep added as it goes for example
Started
# First Transaction
{'date': '01/12/2024', 'tickers': [{'ticker': APPL, 'shares': 20}], 'total_value': 4000.00}
# I purchased 20 in NVDA
{'date': '03/12/2024', 'tickers': [{'ticker': APPL, 'shares': 20}, {'ticker': NVDA, 'shares': 20}}], 'total_value': 5000.00}
# I purchased 5 more in APPL
{'date': '08/12/2024', 'tickers': [{'ticker': APPL, 'shares': 25}, {'ticker': NVDA, 'shares': 20}}], 'total_value': 7000.00}
So, I want historical value at each transaction date so I can create char in excel with these historical data
but the issue with this script is it gives me the total of each transaction alone
The full script :
import pandas as pd
import yfinance as yf
from datetime import timedelta, datetime
# Load data
file = "Finance.xlsx"
df = pd.read_excel(file, sheet_name="Data", header=2)
# Normalize column names
df.columns = df.columns.str.strip().str.upper()
# Clean and sort data
df['DATE'] = pd.to_datetime(df['DATE'], errors='coerce')
df = df.dropna(subset=['DATE']).sort_values(by='DATE')
# Dictionary to track cumulative shares per ticker
cumulative_shares = {}
# Initialize list to store dictionaries
data_list = []
# Custom price list for missing data
missing_prices = {
4071: {
'2021-11-15': 104.0,
'2021-11-16': 104.0,
'2021-11-17': 103.80,
'2021-11-18': 102.4,
'2021-11-21': 100.6,
'2021-11-22': 95.4,
'2021-11-23': 97.5,
'2021-11-24': 96.50,
'2021-11-25': 94.00,
'2021-11-28': 86.30,
'2021-11-29': 90.10,
'2021-11-30': 88.30,
'2021-12-01': 87.40,
'2021-12-02': 88.20,
'2021-12-05': 89.80,
'2021-12-06': 89.80,
'2021-12-07': 91.60,
'2021-12-08': 91.60,
'2021-12-09': 89.00,
'2021-12-12': 88.60,
'2021-12-13': 88.70,
'2021-12-14': 88.00,
'2021-12-15': 88.40,
'2021-12-16': 89.80,
'2021-12-19': 88.20,
'2021-12-20': 86.80,
# Add other dates and prices as needed
}
}
# Function to get the close price from Yahoo Finance or custom list
def get_close_price(ticker, target_date, lookback_days=30):
"""
Retrieves the Close price for the given ticker on the target date.
If data for the target date isn't available, it fetches the Close price
from the most recent available date before the target date within the lookback period.
Parameters:
- ticker (int): The stock ticker symbol (e.g., 2222).
- target_date (pd.Timestamp): The target date.
- lookback_days (int): Number of days to look back for available data.
Returns:
- float: The Close price if available, else 0.
"""
try:
if not isinstance(target_date, pd.Timestamp):
raise ValueError("Invalid date format")
target_date_str = target_date.strftime('%Y-%m-%d')
# Check for custom price list
if ticker in missing_prices and target_date_str in missing_prices[ticker]:
return missing_prices[ticker][target_date_str]
ticker_full = str(ticker) + ".SR" # Add .SR to ticker
# Define the date range for fetching data
start_date = (target_date - timedelta(days=lookback_days)).strftime('%Y-%m-%d')
end_date = (target_date + timedelta(days=1)).strftime('%Y-%m-%d') # Make end date inclusive
# Download historical data using yfinance
data = yf.download(ticker_full, start=start_date, end=end_date, progress=False, auto_adjust=True)
if data.empty:
print(f"No data available for {ticker_full} between {start_date} and {end_date}.")
return 0
# Ensure data.index is timezone-naive
if data.index.tz is not None:
data.index = data.index.tz_convert(None)
# Filter data up to and including the target date
data = data[data.index <= target_date]
if data.empty:
print(f"No data on or before {target_date.strftime('%Y-%m-%d')} for {ticker_full}.")
return 0
# Get the last available Close price
last_available_date = data.index[-1]
last_close_price = data['Close'].iloc[-1]
if last_available_date.date() == target_date.date():
print(f"Close price on {last_available_date.strftime('%Y-%m-%d')} for {ticker_full}: {last_close_price}")
else:
print(f"No data for {target_date.strftime('%Y-%m-%d')} for {ticker_full}. Using Close price from {last_available_date.strftime('%Y-%m-%d')}: {last_close_price}")
return last_close_price
except yf.shared._exceptions.YFinanceError:
print(f"{ticker_full}: possibly delisted or invalid ticker")
return 0
except Exception as e:
print(f"Error fetching data for {ticker_full} on {target_date.strftime('%Y-%m-%d')}: {e}")
return 0
# Process each row to create the required structure
for _, row in df.iterrows():
# Extract fields
date = row['DATE']
date_str = date.strftime('%d/%m/%Y') # Format the date as 'dd/mm/yyyy'
ticker = row['TICKER']
shares = row['NUMBER OF SHARES']
cost_per_share = row['COST / DIVIDENDS PER SHARE'] if not pd.isna(row['COST / DIVIDENDS PER SHARE']) else 0
transaction_type = row['TYPE']
# Skip dividends
if transaction_type == 'DIVIDENDS':
continue
# Calculate cumulative shares
if ticker not in cumulative_shares:
cumulative_shares[ticker] = 0
if transaction_type == 'SOLD':
cumulative_shares[ticker] -= shares
else:
cumulative_shares[ticker] += shares
# Get close price and calculate total value
close_price = get_close_price(ticker, date)
total_value = cumulative_shares[ticker] * close_price
# Ensure total_value is a simple numeric type
if isinstance(total_value, pd.Series):
total_value = total_value.iloc[0]
# Create the dictionary for the row
transaction_dict = {
'date': date_str,
'type': transaction_type,
'ticker': ticker,
'shares': cumulative_shares[ticker], # Cumulative shares
'cost_per_share': cost_per_share,
'total_value': total_value # Add total value
}
# Append to the list
data_list.append(transaction_dict)
# Example output
for transaction in data_list:
print(transaction)
the issue with this script is it gives me the total of each transaction alone