Home icon

/ 博客/

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

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

作者：Anaconda 团队

当我们揭示在 Excel 中使用 Python 的强大功能时，我们已经涵盖了一些基础知识，从理解 基础知识 到亲身实践 时间序列数据分析。 在此基础上，今天我们将深入探索在 Excel 舒适环境中的股票数据分析世界。

为何进行股票数据分析？

在一个数据驱动的世界中，金融市场也不例外。高效地分析股票数据可能是解锁潜在投资机会和避开金融陷阱的关键。Excel 作为一个强大的工具，为许多专业人士提供了熟悉的环境。将其与 Python 的分析能力相结合，您就拥有了强大的工具集。

准备阶段

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

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

数据摄取

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

  • 定期测量的温度
  • 几个月内每月的出租车总乘坐次数
  • 一家公司 2021 年每天记录的股票价格

为了演示 pandas 在股票分析中的应用，我们将使用 2013 年至 2018 年的亚马逊股票价格。这里需要指出的是，Anaconda 将 Anaconda Distribution for Python 无缝集成到 Excel 中。这种集成不仅带来了基本的 Python 功能，还允许访问 400 多个精选软件包。

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

# Importing required modules
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Settings for pretty nice plots
plt.style.use('fivethirtyeight')
# Read in the data as a DataFrame
data = xl("data!A1:I1317", headers=True)

Import modules

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

DataFrame 的强大功能

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

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

  1. 作为 Python 对象：单元格将显示术语 DataFrame，前面带有一个卡片图标。通过选择此卡片图标或使用 Ctrl+Shift+F5，您可以查看 DataFrame 中的数据。
Dataframe
  1. 转换为 Excel 值：除了将其视为 Python 对象外，您还可以将 DataFrame 直接输出为 Excel 值。如果您想对数据执行进一步的 Excel 特定操作，例如创建图表或使用 Excel 公式，这将特别有用。
Python

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

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

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

让我们首先查看数据集的前几列

# Inspecting the data
data.head()

Main image of Excel field

然后，您可以根据您的需求决定将此数据显示为 Python 对象还是 Excel 值。让我们删除前两列，因为它们不会为数据添加任何价值。

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

Visualizing data within Excel

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

# Convert string to datetime64
data['Date'] = data['Date'].apply(pd.to_datetime)

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

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

Convert string to datetime

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

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

Adj_Close 列表示调整后的收盘价，或任何给定交易日股票的收盘价，经过修正以包括在下一个交易日开盘前的任何时间发生的任何分配和/或公司行为。在检查或执行历史回报率的详细分析时，通常使用调整后的收盘价。

在 Excel 中可视化数据

通过 Python 在 Excel 中的集成，我们可以直接在 Excel 中利用著名的 Python 库（如 seaborn 和 matplotlib）的可视化功能。让我们可视化调整后的收盘价，以了解一段时间内的收盘价模式。

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

默认情况下，可视化效果以图像对象的形式返回。以下是如何使用它们

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

看起来亚马逊的股价在 2013-2018 年期间或多或少稳定增长。我们现在将使用 pandas 分析和操作此数据，以收集更多见解。

高级分析：时间重采样

在金融机构领域，发现市场趋势至关重要，检查每日股票价格数据并不总是实际的。为了解决这个问题，采用了一种称为时间重采样的过程，该过程将数据聚合到定义的时间段内，如月份或季度。这使机构可以大致了解股票价格，并根据这些趋势做出决策。

pandas 库为此目的提供了一个 resample() 函数，其功能类似于 groupby 方法，通过按特定的时间跨度对数据进行分组。resample() 函数的实现方式如下

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

总结

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

输出结果为每年 12 月 31 日显示的平均股票数据。

Spreadsheet fields

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

List of fields

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

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

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

高级分析：滚动窗口

时间序列数据通常包含由于市场波动而产生的大量噪声，这使得识别趋势或模式变得具有挑战性。考虑一下亚马逊多年来调整后收盘价的可视化效果

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

Fluctuation chart

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

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

Seven day window calc

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

那么，计算移动平均线或使用滚动平均线方法的关键优势是什么？它显着减少了我们数据中的噪声，使其比原始数据更能指示潜在趋势。让我们通过绘制原始数据，然后绘制 30 天滚动数据来可视化这一点

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

Plot chart

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

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

Parul Pandey 是 H2O.ai 的首席数据科学家，专门从事产品和社区交叉领域的前沿 AI 进步。作为《高风险应用机器学习》一书的合著者，她的工作重点是责