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）在目錄中找到我們需要的幣種代碼；
  • 功能 TEXT （文本），它通過斜杠根據給定的模式日-月-年轉換日期。

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 語言的更多信息。

重要的是輸入公式後不要按 Enter, 和鍵盤快捷鍵 按Ctrl+轉移+Enter，即以數組公式的形式輸入（其周圍的花括號將自動添加）。 如果您擁有支持 Excel 中的動態數組的最新版 Office 365，那麼一個簡單的 Enter，並且您不需要提前選擇空單元格 - 函數本身將根據需要獲取盡可能多的單元格。

為了提取日期，我們將執行相同的操作——我們將在相鄰列中選擇幾個空單元格並使用相同的函數，但使用不同的 XPath 查詢，從 Record 標籤中獲取 Date 屬性的所有值：

=FILTER.XML(B8;”//記錄/@Date”)

現在將來，當更改原始單元格 B2 和 B3 中的日期或在單元格 B3 的下拉列表中選擇不同的貨幣時，我們的查詢將自動更新，參考中央銀行服務器獲取新數據。 要手動強制更新，您還可以使用鍵盤快捷鍵 按Ctrl+其他+F9.

  • 通過 Power Query 將比特幣匯率導入 Excel
  • 在舊版 Excel 中從 Internet 導入匯率

發表評論