使用 Python 在 Excel 中深入探索股票数据分析

在我们揭开在 Excel 中使用 Python 的力量时，我们已经从了解基础知识到亲自动手进行时间序列数据分析，涵盖了一些内容。在此基础上，今天我们将深入了解股票数据分析的世界，并在 Excel 的舒适环境中进行分析。

为什么要进行股票数据分析？

在数据驱动的世界中，金融市场也不例外。高效地分析股票数据可能是打开潜在投资机会并避开财务陷阱的关键。Excel 作为功能强大的工具，为许多专业人士提供熟悉的环境。将它与 Python 的分析能力相结合，您将获得一套强大的工具。

准备阶段

在我们深入研究之前，强烈建议新读者熟悉本系列文章中之前发布的要点。以下是这些文章的快速链接

• Python for Excel Analysts: The Basics
• Analyzing Time Series Data with Python in Excel

拥有坚实的基础将帮助您充分利用我们即将探索的先进技术。

数据摄取

为了进行本练习，让我们假设我们在此 Excel 文件中有一些时间序列股票数据。时间序列数据是按时间顺序排列的数据点序列，企业使用它来分析过去数据并做出未来预测。这些数据点是在指定时间和相等间隔内进行的一组观测，通常具有日期时间索引和相应的数值。我们日常生活中常见的时间序列数据示例包括

• 在定期间隔内测量的温度
• 一系列月份的总月度出租车乘坐次数
• 2021 年每天记录的公司的股票价格

为了演示 pandas 在股票分析中的应用，我们将使用 2013 年至 2018 年的亚马逊股票价格。这里需要指出的是，Anaconda 将 Python 的 Anaconda 发行版无缝集成到 Excel 中。这种集成不仅带来了基本的 Python 功能，还提供了对超过 400 个软件包的精选集合的访问权限。

让我们开始数据分析过程。我们已经在工作簿（名为“data”）中拥有数据，并希望将其读入 pandas DataFrame。我们将在同一个工作簿中创建一个名为“Analysis”的新工作表，并输入：=py 在 Excel 单元格中，然后输入以下代码

# 导入所需的模块 import pandas as pd import numpy as np import matplotlib.pyplot as plt # 设置漂亮的绘图 plt.style.use('fivethirtyeight')

# 将数据读取为 DataFrame data = xl("data!A1:I1317", headers=True)

在此示例中，您将从“data”选项卡中指定范围“data!A1:I1317”读取数据。参数“headers=True”表示第一行包含列标题，使 Excel 能够创建具有正确列名称的 pandas DataFrame。

DataFrame 的强大功能

在继续之前，让我们花些时间了解 DataFrame 的概念。DataFrame 是一种二维数据结构，类似于 Excel 表格。但在 Python 的环境中，它是强大 pandas 库中的一个对象。当您在 Excel 中与 Python 交互时，您主要是在与 DataFrame 交互，使其成为二维范围的默认对象。

当 DataFrame 在 Excel 中使用 Python 创建或处理时，它可以通过两种主要方式可视化

1. 作为 Python 对象：该单元格将显示 DataFrame 术语，前面带有卡片图标。通过选择此卡片图标或使用 Ctrl+Shift+F5，您可以查看 DataFrame 中的数据。

2. 转换为 Excel 值：除了将其视为 Python 对象之外，您还可以将 DataFrame 直接输出为 Excel 值。如果您想对数据执行更多 Excel 特定的操作，例如创建图表或使用 Excel 公式，这将非常有用。

提示：使用 Python 输出菜单或快捷键 Ctrl+Alt+Shift+M 轻松切换这些可视化效果。

Python 在 Excel 中与 pandas 相遇：使用 pandas 分析数据

利用 DataFrame 的概念，我们可以使用强大的 pandas 库来获得见解。

让我们从查看数据集的前几列开始

# 检查数据 data.head()

然后，您可以根据自己的需要选择将此数据显示为 Python 对象或 Excel 值。让我们删除前两列，因为它们对数据没有增加任何价值。

data.drop(columns=['None', 'ticker'], inplace=True) data.head()

接下来，我们将使用 pandas 的 to_datetime() 功能，它将参数转换为日期。

# 将字符串转换为 datetime64 data['Date'] = data['Date'].apply(pd.to_datetime)

最后，我们要确保“date”列是索引列。

data.set_index('Date', inplace=True) data.head()

现在我们的数据已转换为所需的格式，让我们查看它的列以进行进一步分析。

• Open 和 Close 列分别表示特定日期的股票开盘价和收盘价。
• High 和 Low 列分别提供特定日期的股票最高价和最低价。
• Volume 列告诉我们特定日期的股票交易总量。

Adj_Close 列表示调整后的收盘价，或任何特定交易日的股票收盘价，已调整为包括在下一交易日开盘之前发生的任何分配和/或公司行动。调整后的收盘价通常用于检查或对历史回报进行详细分析。

在 Excel 中可视化数据

通过将 Python 集成到 Excel 中，我们可以直接在 Excel 中利用 seaborn 和 matplotlib 等著名 Python 库的可视化功能。让我们可视化调整后的收盘价，以了解收盘价随时间的变化模式。

data['Adj_Close'].plot(figsize=(16,8),title='Adjusted Closing Price')

默认情况下，可视化效果将作为图像对象返回。以下是如何处理它们

• 预览：单击图像对象单元格中的卡片图标。
• 嵌入到 Excel 中：使用 Python 输出菜单将图像对象转换为 Excel 值。
• 调整大小和重新定位：使用“创建引用”按钮生成图像的浮动副本，可以根据需要进行调整。
  
  

看来亚马逊在 2013 年至 2018 年期间的股票价格总体上呈稳定增长趋势。我们现在将使用 pandas 来分析和操作这些数据，以获取更多见解。

高级分析：时间重采样

在金融机构中，识别市场趋势至关重要，因此每天检查股票价格数据并不总是切合实际。为了解决这个问题，人们采用了时间重采样过程，它将数据聚合到定义的时间段，例如月份或季度。这使机构能够全面了解股票价格，并根据这些趋势做出决策。

pandas 库为此目的提供了 resample() 函数，该函数类似于 groupby 方法，它根据特定时间跨度对数据进行分组。resample() 函数的实现方式如下

data.resample(rule = 'A').mean()

总结一下

• data.resample() 用于对股票数据进行重采样。
• A 代表年终频率，表示我们希望对数据进行重采样的偏移值。
• mean() 表示我们想要此期间的平均股票价格。

输出以每年 12 月 31 日显示的平均股票数据的形式呈现。

可以在 pandas 文档中找到偏移值的完整列表，如下所示

时间采样也可以用于为特定列生成图表。例如

data['Adj_Close'].resample('A').mean().plot(kind='bar',figsize = (10,4)) plt.title('Yearly Mean Adj Close Price for Amazon')

上面的条形图说明了亚马逊在每个年末的数据集中调整后的平均收盘价。同样，您可以找到每年每月最大的开盘价。

高级分析：滚动窗口

时间序列数据通常包含大量噪声，这是由于市场波动造成的，这使得很难辨别趋势或模式。考虑一下亚马逊调整后的收盘价在过去几年中的可视化效果

data['Adj_Close'].plot(figsize = (16,8))

鉴于每天的数据频率，明显的噪声很大。平滑这种噪声将是有益的，这就是滚动平均值变得有价值的地方。滚动平均值，也称为移动平均值，是一种转换技术，通过使用各种函数（如 mean()、median()、count() 等）将数据拆分并聚合到窗口中来减少数据中的噪声。在我们的示例中，我们将使用 7 天窗口的滚动平均值

data.rolling(7).mean().head(10)

输出显示前六个值为空，因为没有足够的数据来计算 7 天窗口的滚动平均值。

那么，计算移动平均数或使用滚动均值方法的主要优势是什么？它能显著减少数据中的噪声，使其比原始数据更能反映潜在趋势。让我们通过绘制原始数据和 30 天滚动数据来可视化这一点。

data['Open'].plot() data.rolling(window=30).mean()['Open'].plot(figsize=(16, 6))

在上图中，蓝线代表原始开盘价数据，橙线代表 30 天滚动窗口数据，噪声更小。值得注意的是，前 29 天没有橙线，因为在此期间没有足够的数据来计算滚动均值。

在本篇关于 Python 与 Excel 集成进行股票数据分析的探索中，我们发现了将 Excel 的熟悉度与 Python 的分析能力相结合的强大之处。我们学习了如何有效地读取和操作股票数据，使用 seaborn 和 matplotlib 等 Python 库在 Excel 中对其进行可视化，并使用时间重采样和滚动窗口等高级技术来更深入地了解金融趋势。本文表明，Excel 在 Python 的增强下，为数据分析师提供了一个无缝且直观的平台，释放了从复杂的金融数据集中做出明智决策和发现宝贵见解的能力。

Parul Pandey 是 H2O.ai 的首席数据科学家，专门从事产品和社区交叉路口的前沿人工智能进步。作为机器学习用于高风险应用的合著者，她的工作强调负责任的人工智能实施。