使用 Python 进行时间序列分析和预测

CREATE TABLE stock_data ( 
         date DATE, 
         open FLOAT, 
         high FLOAT, 
         low FLOAT, 
         close FLOAT, 
         volume BIGINT, 
         name TEXT 
);

SELECT create_hypertable('stock_data', 'date');

pip install psycopg2
pip install pandas
pip install matplotlib
pip install plotly

con  =  psycopg2.connect(
        host='your_host',
        port='your_port',
        database='your_database',
        user='your_username',
        password='your_password'
)

# Create a cursor
cur = conn.cursor()

query = sql.SQL(SELECT * FROM stock_data LIMIT 10)

# Execute the query
cursor.execute(query)

# Fetch the results
records = cur.fetchall()

# Close the cursor and connection
cur.close()
conn.close()

for record in records:
      print(record)

import yfinance as yf
import pandas as pd
from datetime import datetime, timedelta

# Define the list of stock tickers and the time period
tickers = [
    'AAPL', 'MSFT', 'GOOGL', 'AMZN', 'TSLA', 'FB', 'NVDA', 'NFLX', 'INTC', 'AMD',
    'IBM', 'ORCL', 'CSCO', 'ADBE', 'PYPL', 'QCOM', 'TXN', 'AVGO', 'CRM', 'SAP'
]
start_date = '2019-05-17'
end_date = '2024-05-17'  # Use today's date

# Function to download and format data for a single stock
def download_and_format_stock_data(ticker, start_date, end_date, retries=3):
    for i in range(retries):
        try:
            stock_data = yf.download(ticker, start=start_date, end=end_date)
            if not stock_data.empty:
                stock_data.reset_index(inplace=True)
                stock_data['Name'] = ticker
                stock_data = stock_data[['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Name']]
                stock_data.columns = ['date', 'open', 'high', 'low', 'close', 'volume', 'name']
                return stock_data
        except Exception as e:
            print(f"Attempt {i+1} failed for {ticker}: {e}")
    return pd.DataFrame()

# Download and format data for all tickers
all_stocks_data = pd.concat([download_and_format_stock_data(ticker, start_date, end_date) for ticker in tickers])

# Display the first few rows of the combined data
print(all_stocks_data.head())

# Save to CSV
file_path = 'formatted_stock_data2.csv'
all_stocks_data.to_csv(file_path, index=False)

print(f"Data saved to {file_path}")

import plotly.graph_objects as go
import pandas as pd

max_volume_df_2024 = max_volume_df[(max_volume_df['date'] >= pd.Timestamp('2024-01-01')) & (max_volume_df['date'] <= pd.Timestamp('2024-5-16'))]

fig = go.Figure()

fig.add_trace(go.Bar(x=max_volume_df_2024['date'], y=max_volume_df_2024['volume'], marker_color='purple'))

fig.update_layout(
    title=f'Trading Volume for {max_volume_company["name"]} in 2024',
    xaxis_title='Date',
    yaxis_title='Volume',
    template='plotly_white',
    xaxis_rangeslider_visible=True  # Enable the range slider for zooming
)

fig.show()

# Print summary statistics
print(data.describe())

# Get the row count
row_count = len(data)

# Print formatted output
print("approximate_row_count")
print("----------------------")
print(row_count)

ANALYZE stock_data;
SELECT * FROM approximate_row_count('stock_data');

import pandas as pd
import numpy as np
# Assuming stock_data is already defined and contains the dataset

# Convert 'date' column to datetime if it's not already in datetime format
data['date'] = pd.to_datetime(data['date'])

# Set 'date' as the index
data.set_index('date', inplace=True)

# Group data by daily intervals and calculate statistics for the 'open' price
daily_summary = data['open'].resample('D').agg(
    stats1D=lambda x: (np.mean(x), np.std(x), x.skew()) if not x.empty else np.nan
)

# Remove rows with NaN values (if any)
daily_summary = daily_summary.dropna()

# Print the result
print(daily_summary)

WITH t AS (
    SELECT
        time_bucket('1 day'::interval, "date") AS dt,
        stats_agg(open) AS stats1D
    FROM stock_data
    WHERE Name = 'AAL'
    GROUP BY time_bucket('1 day'::interval, "date")
)
SELECT
    average(stats1D) AS avg_open,
    stddev(stats1D) AS stddev_open,
    skewness(stats1D) AS skewness_open
FROM t;

# Convert the date column to datetime and set it as the index
data['date'] = pd.to_datetime(data['date'])
data.set_index('date', inplace=True)

# Filter for a specific stock,
avgo_df = data[data['name'] == 'AVGO']['close'].asfreq('B')

# Check for stationarity
result = adfuller(avgo_df.dropna())
print('ADF Statistic:', result[0])
print('p-value:', result[1])
# If the series is not stationary, differencing might be necessary

# Fit the ARIMA model
model = ARIMA(avgo_df, order=(5,1,0))  # Example order, needs optimization
model_fit = model.fit()

# Summary of the model
print(model_fit.summary())

# Perform differencing
differenced_series = avgo_df.diff().dropna()  # Compute the difference and remove NaN values

# Check for stationarity after differencing
result_diff = adfuller(differenced_series)
print('ADF Statistic after differencing:', result_diff[0])
print('p-value after differencing:', result_diff[1])

from sklearn.metrics import mean_absolute_error

# Calculate MAE
mae_diff = mean_absolute_error(differenced_series[1:], train_predictions_diff)

print("MAE:", mae_diff)

from sklearn.metrics import mean_squared_error

# Make predictions on the training data
train_predictions_diff = model_fit_diff.predict(start=differenced_series.index[1], end=differenced_series.index[-1])

# Align the indices of the differenced series and predictions
train_predictions_diff = train_predictions_diff[differenced_series.index[1]:]

# Calculate RMSE
rmse_diff = np.sqrt(mean_squared_error(differenced_series[1:], train_predictions_diff))

print("RMSE:", rmse_diff)

import psycopg2
import pandas as pd

# Database credentials
db_url = "postgres://tsdbadmin:[email protected]:33159/tsdb?sslmode=require"

# Connect to the database
conn = psycopg2.connect(db_url)

# Create a cursor
cur = conn.cursor()

# Define the query to fetch data for the past 3 months
query = """
SELECT bucket, open, high, low, close
FROM one_min_candle
WHERE symbol = 'BTC/USD'
AND bucket >= NOW() - INTERVAL '1 months'
ORDER BY bucket DESC;
"""

# Execute the query
cur.execute(query)

# Fetch the results
rows = cur.fetchall()

# Convert the results to a pandas DataFrame
stock_data = pd.DataFrame(rows, columns=['bucket', 'open', 'high', 'low', 'close'])

# Print the DataFrame
print(stock_data)

# Optionally, save the DataFrame to a CSV file
stock_data.to_csv('stock_data.csv', index=False)

# Close the cursor and connection
cur.close()
conn.close()