Excel 中的更新汇率

我反复分析了从 Internet 将数据导入 Excel 并随后自动更新的方法。 尤其是：

  • 在旧版本的 Excel 2007-2013 中，这可以通过直接 Web 请求来完成。
  • 从 2010 年开始，可以使用 Power Query 加载项非常方便地完成此操作。

对于最新版本的 Microsoft Excel 中的这些方法，您现在可以添加另一种方法 - 使用内置函数以 XML 格式从 Internet 导入数据。

XML（可扩展标记语言=可扩展标记语言）是一种通用语言，旨在描述任何类型的数据。 事实上，它是纯文本，只是添加了特殊的标签来标记数据结构。 许多站点以 XML 格式提供免费的数据流供任何人下载。 特别是在我国中央银行的网站（www.cbr.ru）上，在类似技术的帮助下，提供了各种货币的汇率数据。 您可以从莫斯科交易所网站 (www.moex.com) 以同样的方式下载股票、债券和许多其他有用信息的报价。

自 2013 版以来，Excel 具有两个功能，可将来自 Internet 的 XML 数据直接加载到工作表单元格中： 网络服务 （网络服务） и 过滤器.XML (过滤器XML). 它们成对工作——首先是功能 网络服务 向所需站点执行请求并以 XML 格式返回其响应，然后使用函数 过滤器.XML 我们将这个答案“解析”成组件，从中提取我们需要的数据。

让我们用一个经典的例子来看看这些函数的操作——从我们国家的中央银行网站导入我们在给定日期间隔内需要的任何货币的汇率。 我们将使用以下结构作为空白：

Excel 中的更新汇率

这里：

  • 黄色单元格包含我们感兴趣的时期的开始和结束日期。
  • 蓝色的有一个使用命令的货币下拉列表 数据 - 验证 - 列表 （数据——验证——列表）.
  • 在绿色单元格中，我们将使用我们的函数创建一个查询字符串并获取服务器的响应。
  • 右边的表格是对货币代码的引用（稍后我们将需要它）。

我们走吧！

步骤 1. 形成查询字符串

要从该站点获取所需的信息，您需要正确地询问它。 我们去 www.cbr.ru 并打开主页页脚中的链接' 技术资源'- 使用 XML 获取数据 (http://cbr.ru/development/SXML/)。 我们向下滚动一点，在第二个示例（示例 2）中将有我们需要的内容 - 获取给定日期间隔的汇率：

Excel 中的更新汇率

从示例中可以看出，查询字符串必须包含开始日期 (日期_req1) 和结尾 (日期_req2) 我们感兴趣的时期和货币代码 (VAL_NM_RQ)，我们想要得到的比率。 您可以在下表中找到主要货币代码：

货币

代码

                          

货币

代码

澳元兑美元 R01010

立陶宛立特

R01435

奥地利先令

R01015

立陶宛优惠券

R01435

阿塞拜疆马纳特

R01020

摩尔多瓦列伊

R01500

英镑

R01035

РќРµРјРµС † РєР ° СЏ РјР ° СЂРєР °

R01510

安哥拉新宽扎

R01040

荷兰盾

R01523

亚美尼亚德拉姆

R01060

挪威克朗

R01535

白俄罗斯卢布

R01090

波兰兹罗提

R01565

比利时法郎

R01095

葡萄牙埃斯库多

R01570

保加利亚狮子

R01100

罗马尼亚列伊

R01585

巴西雷亚尔

R01115

新加坡元

R01625

匈牙利福林

R01135

苏里南元

R01665

港元

R01200

塔吉克索莫尼

R01670

希腊德拉克马

R01205

塔吉克卢布

R01670

丹麦克朗

R01215

土耳其里拉

R01700

美元

R01235

土库曼马纳特

R01710

欧元

R01239

新土库曼马纳特

R01710

印度卢比

R01270

乌兹别克斯坦

R01717

爱尔兰镑

R01305

乌克兰格里夫纳

R01720

冰岛克朗

R01310

乌克兰 karbovanets

R01720

西班牙比塞塔

R01315

芬兰马克

R01740

意大利里拉

R01325

法国法郎

R01750

哈萨克斯坦坚戈

R01335

捷克克朗

R01760

加拿大元

R01350

瑞典克朗

R01770

吉尔吉斯斯坦

R01370

瑞士弗兰克

R01775

中国元

R01375

爱沙尼亚克朗

R01795

科威特第纳尔

R01390

南斯拉夫新第纳尔

R01804

拉脱维亚拉特

R01405

南非兰特

R01810

黎巴嫩镑

R01420

韩元

R01815

日圆

R01820

中央银行网站上还提供了货币代码的完整指南——参见 http://cbr.ru/scripts/XML_val.asp?d=0

现在我们将在工作表的单元格中形成一个查询字符串：

  • 将文本连接运算符 (&) 放在一起；
  • 特征 VPR （VLOOKUP）在目录中找到我们需要的币种代码；
  • 特征 文字 （文本），它通过斜杠根据给定的模式日-月-年转换日期。

Excel 中的更新汇率

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)

步骤 2. 执行请求

现在我们使用函数 网络服务 （网络服务） 将生成的查询字符串作为唯一参数。 答案将是一长串 XML 代码（如果您想完整查看，最好打开自动换行并增加单元格大小）：

Excel 中的更新汇率

步骤 3. 解析答案

为了更容易理解响应数据的结构，最好使用在线 XML 解析器之一（例如，http://xpather.com/ 或 https://jsonformatter.org/xml-parser），它可以直观地格式化 XML 代码，为其添加缩进并用颜色突出显示语法。 然后一切都会变得更加清晰：

Excel 中的更新汇率

现在您可以清楚地看到课程值是由我们的标签框起来的 ..., 日期是属性 日期 在标签中 .

要提取它们，请在工作表上选择一列 10 个（或更多——如果有边距）空单元格（因为设置了 XNUMX 天的日期间隔），然后在公式栏中输入函数 过滤器.XML （筛选XML）:

Excel 中的更新汇率

在这里，第一个参数是带有服务器响应 (B8) 的单元格的链接，第二个参数是 XPath 中的查询字符串，这是一种特殊的语言，可用于访问必要的 XML 代码片段并提取它们。 例如，您可以在此处阅读有关 XPath 语言的更多信息。

重要的是输入公式后不要按 输入, 和键盘快捷键 按Ctrl+转移+输入，即以数组公式的形式输入（其周围的花括号将自动添加）。 如果您拥有支持 Excel 中的动态数组的最新版 Office 365，那么一个简单的 输入，并且您不需要提前选择空单元格 - 函数本身将根据需要获取尽可能多的单元格。

要提取日期，我们将执行相同的操作——我们将在相邻列中选择几个空单元格并使用相同的函数，但使用不同的 XPath 查询，从 Record 标签中获取 Date 属性的所有值：

=FILTER.XML(B8;”//记录/@Date”)

现在将来，当更改原始单元格 B2 和 B3 中的日期或在单元格 B3 的下拉列表中选择不同的货币时，我们的查询将自动更新，参考中央银行服务器获取新数据。 要手动强制更新，您还可以使用键盘快捷键 按Ctrl+其他+F9.

  • 通过 Power Query 将比特币汇率导入 Excel
  • 在旧版 Excel 中从 Internet 导入汇率

发表评论