如何通过Google API将每日历史股票价格导入pandas DataFrame？

Great question! You’re already halfway there with your intraday Google Finance API setup—adjusting it for daily data just requires tweaking a few parameters and handling date formatting properly.

Step 1: Modify the API Request for Daily Data #

The key changes target the interval and period parameters in your API call:

• i=86400: Sets the interval to 86400 seconds (one full day)
• p=10y: Adjust this to your desired time range (e.g., 5y for 5 years, 6m for 6 months, 1d for a single day)
• Keep the f parameter for OHLC data, or add v if you want to include trading volume

Here’s the modified code snippet:

import pandas as pd

# Request 10 years of daily SPY prices
api = 'http://finance.google.com/finance/getprices?q=SPY&i=86400&p=10y&f=d,o,h,l,c,v'
df = pd.read_csv(api, skiprows=8, header=None)

Step 2: Clean Up the Date Column #

Google’s API returns dates in a quirky format: the first row uses a Unix timestamp, and subsequent rows use day offsets (e.g., +1 means one day after the initial timestamp). Here’s how to convert this to proper datetime values:

# Rename columns for clarity
df.columns = ['Date', 'Open', 'High', 'Low', 'Close', 'Volume']

# Extract the base timestamp from the first row
base_timestamp = int(df.iloc[0]['Date'].split('=')[1])
base_date = pd.to_datetime(base_timestamp, unit='s')

# Convert offset rows to actual dates
df['Date'] = df['Date'].apply(
    lambda x: base_date + pd.Timedelta(days=int(x[1:])) if x.startswith('+') else base_date
)

# Optional: Set Date as the DataFrame index
df.set_index('Date', inplace=True)

Heads Up: Deprecation of Google’s Legacy API #

Keep in mind that Google’s old finance API is no longer officially supported and may stop working without warning. For a more reliable solution, consider using the yfinance library (a popular, maintained replacement for Yahoo Finance data):

import yfinance as yf

# Fetch 10 years of daily SPY data with minimal setup
df = yf.download('SPY', period='10y', interval='1d')

This gives you a pre-formatted DataFrame with all standard OHLCV data—no extra cleaning required!

内容的提问来源于stack exchange，提问作者pmillerhk