def fetchstockdata(ticker, startdate): """ Fetch historical stock data from database…
- User
def fetch_stock_data(ticker, start_date): """ Fetch historical stock data from database first, then yfinance if needed. Also checks for earliest available date. """ try: start_date = pd.to_datetime(start_date) ticker = ticker.strip().upper() today = pd.Timestamp.now().normalize()
# First check yfinance for earliest available date try: # Use a more recent date for the initial check (e.g., 1970) earliest_check = yf.download( ticker, start='1970-01-01', end=today, progress=False, auto_adjust=False, timeout=30 ) if earliest_check.empty: logger.error(f"No data available from yfinance for {ticker}") return None earliest_available = earliest_check.index[0] logger.info(f"Earliest available date for {ticker}: {earliest_available}") # If requested start date is before earliest available if start_date < earliest_available: logger.warning(f"Adjusting start date from {start_date} to {earliest_available} for {ticker}") start_date = earliest_available except Exception as e: logger.error(f"Error checking earliest date for {ticker}: {str(e)}") return None # Get sector info and proceed with normal data fetch db = SessionLocal() try: sector_query = text(""" SELECT gics_sector, gics_sub_industry FROM market_tickers WHERE ticker = :ticker """) sector_info = db.execute(sector_query, {"ticker": ticker}).first() # Get data from database stock_query = text(""" SELECT date, open, high, low, close, volume FROM stock_data WHERE ticker = :ticker AND date >= :start_date ORDER BY date """) result = db.execute( stock_query, {"ticker": ticker, "start_date": start_date} ) # Convert result to DataFrame db_data = pd.DataFrame(result.fetchall(), columns=['Date', 'Open', 'High', 'Low', 'Close', 'Volume']) if not db_data.empty: db_data.set_index('Date', inplace=True) db_data['Adj Close'] = db_data['Close'] if sector_info: db_data['gics_sector'] = sector_info[0] db_data['gics_sub_industry'] = sector_info[1] latest_db_date = db_data.index.max() # If data is up to date and covers the entire requested period, return it if latest_db_date >= today - pd.Timedelta(days=1) and db_data.index[0] <= start_date: logger.info(f"Using database data for {ticker}") return db_data # If we need to fetch new data or backfill historical data if db_data.index[0] > start_date: # Need to fetch historical data - use the adjusted start_date historical_data = yf.download( ticker, start=start_date, # Use adjusted start_date end=db_data.index[0], progress=False, auto_adjust=False, timeout=30 ) if not historical_data.empty: if sector_info: historical_data['gics_sector'] = sector_info[0] historical_data['gics_sub_industry'] = sector_info[1] save_stock_data_to_db(ticker, historical_data) db_data = pd.concat([historical_data, db_data]) if latest_db_date < today - pd.Timedelta(days=1): # Need to fetch recent data new_data = yf.download( ticker, start=latest_db_date + pd.Timedelta(days=1), end=today, progress=False, auto_adjust=False, timeout=30 ) if not new_data.empty: if sector_info: new_data['gics_sector'] = sector_info[0] new_data['gics_sub_industry'] = sector_info[1] save_stock_data_to_db(ticker, new_data) db_data = pd.concat([db_data, new_data]) return db_data # If no data in database, fetch all from yfinance logger.info(f"Fetching all data for {ticker} from yfinance") stock_data = yf.download( ticker, start=start_date, end=today, progress=False, auto_adjust=False, timeout=30 ) if not stock_data.empty: if sector_info: stock_data['gics_sector'] = sector_info[0] stock_data['gics_sub_industry'] = sector_info[1] save_stock_data_to_db(ticker, stock_data) return stock_data logger.warning(f"No data available for {ticker}") return None finally: db.close() except Exception as e: logger.error(f"Failed to fetch data for {ticker}: {str(e)}", exc_info=True) return Noneenhance the above functions based on below and return me full updated code: Key changes made: Removed the initial yfinance check for earliest available date since we're using database data Prioritized database data retrieval Only fetch from yfinance if: No data exists in database We need newer data (data in database is not up to date) Added better error handling and logging This should significantly improve the performance since it: Reduces unnecessary API calls to yfinance Uses database data as the primary source Only makes API calls when absolutely necessary The function will now: First check the database for data Use that data if available Only fetch from yfinance if needed for newer data Fall back to yfinance only if no data exists in database This should prevent the long delays you're experiencing while waiting for yfinance API calls.
- Assistant
def fetch_stock_data(ticker, start_date): """ Fetch historical stock data from database first, then yfinance if needed. Checks the database first for existing data, uses yfinance to fetch missing data. """ try: start_date = pd.to_datetime(start_date) ticker = ticker.strip().upper() today = pd.Timestamp.now().normalize() # Start database session db = SessionLocal() try: # Get sector info sector_query = text(""" SELECT gics_sector, gics_sub_industry FROM market_tickers WHERE ticker = :ticker """) sector_info = db.execute(sector_query, {"ticker": ticker}).first() # Get data from database stock_query = text(""" SELECT date, open, high, low, close, adj_close, volume FROM stock_data WHERE ticker = :ticker AND date >= :start_date ORDER BY date """) result = db.execute( stock_query, {"ticker": ticker, "start_date": start_date} ) # Convert result to DataFrame db_data = pd.DataFrame(result.fetchall(), columns=['Date', 'Open', 'High', 'Low', 'Close', 'Adj Close', 'Volume']) if not db_data.empty: db_data.set_index('Date', inplace=True) if sector_info: db_data['gics_sector'] = sector_info[0] db_data['gics_sub_industry'] = sector_info[1] earliest_db_date = db_data.index.min() latest_db_date = db_data.index.max() # Flag to indicate whether we have full data has_full_data = True # Check if we need to backfill historical data if earliest_db_date > start_date: logger.info(f"Need to backfill historical data for {ticker}") try: # Fetch historical data from yfinance historical_data = yf.download( ticker, start=start_date, end=earliest_db_date, progress=False, auto_adjust=False, timeout=30 ) if not historical_data.empty: historical_data.index.name = 'Date' # Standardize column names to match database historical_data = historical_data.rename(columns={'Adj Close': 'Adj_Close'}) if sector_info: historical_data['gics_sector'] = sector_info[0] historical_data['gics_sub_industry'] = sector_info[1] save_stock_data_to_db(ticker, historical_data) historical_data = historical_data[['Open', 'High', 'Low', 'Close', 'Adj_Close', 'Volume']] db_data = pd.concat([historical_data, db_data]) else: logger.warning(f"No historical data fetched from yfinance for {ticker}") has_full_data = False # We couldn't get the historical data except Exception as e: logger.error(f"Error fetching historical data from yfinance for {ticker}: {str(e)}", exc_info=True) has_full_data = False # Check if we need to fetch recent data if latest_db_date < today - pd.Timedelta(days=1): logger.info(f"Need to fetch recent data for {ticker}") try: new_data = yf.download( ticker, start=latest_db_date + pd.Timedelta(days=1), end=today, progress=False, auto_adjust=False, timeout=30 ) if not new_data.empty: new_data.index.name = 'Date' new_data = new_data.rename(columns={'Adj Close': 'Adj_Close'}) if sector_info: new_data['gics_sector'] = sector_info[0] new_data['gics_sub_industry'] = sector_info[1] save_stock_data_to_db(ticker, new_data) new_data = new_data[['Open', 'High', 'Low', 'Close', 'Adj_Close', 'Volume']] db_data = pd.concat([db_data, new_data]) else: logger.warning(f"No recent data fetched from yfinance for {ticker}") has_full_data = False # We couldn't get the recent data except Exception as e: logger.error(f"Error fetching recent data from yfinance for {ticker}: {str(e)}", exc_info=True) has_full_data = False db_data.sort_index(inplace=True) if has_full_data: logger.info(f"Using combined database and yfinance data for {ticker}") return db_data else: logger.warning(f"Incomplete data for {ticker}, returning data we have") return db_data # Return whatever data we have else: # No data in database, fetch all data from yfinance logger.info(f"No data in database for {ticker}, fetching all data from yfinance") try: stock_data = yf.download( ticker, start=start_date, end=today, progress=False, auto_adjust=False, timeout=30 ) if not stock_data.empty: stock_data.index.name = 'Date' stock_data = stock_data.rename(columns={'Adj Close': 'Adj_Close'}) if sector_info: stock_data['gics_sector'] = sector_info[0] stock_data['gics_sub_industry'] = sector_info[1] save_stock_data_to_db(ticker, stock_data) stock_data = stock_data[['Open', 'High', 'Low', 'Close', 'Adj_Close', 'Volume']] return stock_data logger.warning(f"No data available for {ticker}") return None except Exception as e: logger.error(f"Error fetching data from yfinance for {ticker}: {str(e)}", exc_info=True) return None except Exception as e: logger.error(f"Error fetching data for {ticker}: {str(e)}", exc_info=True) return None finally: db.close() except Exception as e: logger.error(f"Failed to fetch data for {ticker}: {str(e)}", exc_info=True) return NoneKey changes made:
-
Removed the initial yfinance check for the earliest available date. Now the function prioritizes fetching data from the database without checking yfinance first.
-
Prioritized database data retrieval. The function now checks the database for existing data and uses it as the primary source.
-
Only fetches from yfinance if needed:
- No data exists in the database: If there is no data available for the requested ticker, it fetches all data from yfinance.
- We need newer data: If the latest date in the database data is not up to date (i.e., doesn't include recent data), it fetches the recent data from yfinance.
- We need to backfill historical data: If the earliest date in the database is after the requested
start_date, it fetches the missing historical data from yfinance.
-
Added better error handling and logging:
- Wrapped yfinance download calls in
try-exceptblocks to catch and log any exceptions that may occur during data fetching. - Logged detailed error messages with
exc_info=Trueto include stack traces in the logs. - Provided informative logging at each step to trace the flow and any potential issues.
- Wrapped yfinance download calls in
Benefits of these changes:
-
Improved Performance:
- Reduces unnecessary API calls to yfinance: By removing the initial check and only calling yfinance when necessary, the function reduces the number of external API calls.
- Uses database data as the primary source: By prioritizing the database, the function relies on faster, local data retrieval.
- Only makes API calls when absolutely necessary: Fetches data from yfinance only to fill gaps, minimizing network latency and potential API rate limits.
-
Enhanced Reliability:
- Better error handling: Improved exception handling ensures that the function can handle unexpected issues gracefully without crashing.
- Informative logging: Detailed logs help in diagnosing issues quickly, improving maintenance and support.
Function Workflow:
-
Input Processing:
- Validates and formats the
tickerandstart_date. - Sets
todayas the current date.
- Validates and formats the
-
Database Check:
- Attempts to fetch sector information and historical stock data from the database.
- If data is available:
- Determines if any data is missing (either historically or the most recent data).
- Fetches missing data from yfinance if necessary.
- Saves any new data fetched from yfinance back to the database.
- Returns the complete dataset.
- If no data is available:
- Fetches all required data from yfinance.
- Saves the new data to the database.
- Returns the dataset.
-
Error Handling:
- Catches and logs any exceptions during data fetching from the database or yfinance.
- Ensures that the database session is closed properly in the
finallyblock.
Notes:
- The function assumes the existence of a
save_stock_data_to_dbfunction, which handles saving the DataFrame to the database. - It also assumes that the
loggeris properly configured elsewhere in your code. - The function standardizes column names to match between yfinance data and your database schema.
- The use of
auto_adjust=Falsein yfinance ensures that you get the raw data without any adjustments, matching typical database storage requirements.
By making these changes, the function should now operate more efficiently and be better aligned with your needs, avoiding long delays due to unnecessary yfinance API calls.
-