如何在Google Sheet中高效计算股票的近3个月平均成交量？

Got it, let's cut out the tedious manual work and solve this efficiently. Instead of exporting all data to Google Sheets and summing manually, you can use Python's yfinance library to pull exactly the data you need and calculate the total volume in seconds. Here's how:

Step 1: Install the Required Library #

First, install yfinance if you haven't already—this is a reliable, easy-to-use tool for fetching stock market data:

pip install yfinance

Step 2: Write the Calculation Code #

This script will pull the last 3 months of daily volume data for your target stock, sum it up, and print the result. Just replace AAPL with your stock's ticker symbol:

import yfinance as yf
from datetime import datetime, timedelta

# Define your stock ticker and 3-month time range
stock_ticker = "AAPL"
end_date = datetime.today()
start_date = end_date - timedelta(days=90)  # Covers roughly 3 months

# Fetch historical daily data
stock_data = yf.download(stock_ticker, start=start_date, end=end_date)

# Calculate total volume over the period
total_volume = stock_data['Volume'].sum()

print(f"Total 3-month volume for {stock_ticker}: {total_volume:,}")

Bonus: Automatically Send Results to Google Sheets (No Manual Copy-Paste) #

If you still need the data in Google Sheets but hate the manual import, you can extend the script to push the result directly using gspread and pandas—no more copying rows by hand:

1. Install the extra libraries first:

pip install gspread pandas gspread-dataframe

2. Add this code after calculating total_volume (you'll need a one-time setup of Google Sheets API credentials, which Google walks you through in their docs):

import gspread
from gspread_dataframe import set_with_dataframe
import pandas as pd

# Initialize Google Sheets client with your credentials file
gc = gspread.service_account(filename='your_credentials.json')

# Open your target Google Sheet
sheet = gc.open('Your Sheet Name').sheet1

# Create a dataframe with your result to append
result_df = pd.DataFrame({
    'Stock Ticker': [stock_ticker],
    '3-Month Total Volume': [total_volume],
    'Calculation Date': [end_date.strftime('%Y-%m-%d')]
})

# Append the data to the bottom of the sheet
set_with_dataframe(sheet, result_df, row=sheet.row_count+1, include_index=False)

This approach eliminates the slow manual process entirely—you can even schedule the script to run automatically if you need regular updates.

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