एक्सेल में अद्यतन विनिमय दर

मैंने बाद में स्वचालित अद्यतन के साथ इंटरनेट से एक्सेल में डेटा आयात करने के तरीकों का बार-बार विश्लेषण किया है। विशेष रूप से:

• एक्सेल 2007-2013 के पुराने संस्करणों में, यह सीधे वेब अनुरोध के साथ किया जा सकता है।
• 2010 से, यह Power Query ऐड-इन के साथ बहुत आसानी से किया जा सकता है।
  

माइक्रोसॉफ्ट एक्सेल के नवीनतम संस्करणों में इन विधियों के लिए, अब आप एक और जोड़ सकते हैं - अंतर्निहित कार्यों का उपयोग करके एक्सएमएल प्रारूप में इंटरनेट से डेटा आयात करना।

एक्सएमएल (एक्स्टेंसिबल मार्कअप लैंग्वेज = एक्सटेंसिबल मार्कअप लैंग्वेज) एक सार्वभौमिक भाषा है जिसे किसी भी तरह के डेटा का वर्णन करने के लिए डिज़ाइन किया गया है। वास्तव में, यह सादा पाठ है, लेकिन डेटा संरचना को चिह्नित करने के लिए इसमें विशेष टैग जोड़े गए हैं। कई साइटें किसी को भी डाउनलोड करने के लिए एक्सएमएल प्रारूप में अपने डेटा की मुफ्त स्ट्रीम प्रदान करती हैं। सेंट्रल बैंक ऑफ अवर कंट्री (www.cbr.ru) की वेबसाइट पर, विशेष रूप से, इसी तरह की तकनीक की मदद से, विभिन्न मुद्राओं की विनिमय दरों पर डेटा दिया जाता है। मॉस्को एक्सचेंज की वेबसाइट (www.moex.com) से आप उसी तरह स्टॉक, बॉन्ड और कई अन्य उपयोगी जानकारी के लिए उद्धरण डाउनलोड कर सकते हैं।

संस्करण 2013 के बाद से, एक्सेल के पास इंटरनेट से एक्सएमएल डेटा को वर्कशीट सेल में सीधे लोड करने के लिए दो कार्य हैं: वेब सेवा (वेब सेवा) и फ़िल्टर.एक्सएमएल (फ़िल्टरएक्सएमएल). वे जोड़ियों में काम करते हैं - पहला कार्य वेब सेवा वांछित साइट के लिए अनुरोध निष्पादित करता है और एक्सएमएल प्रारूप में अपनी प्रतिक्रिया देता है, और फिर फ़ंक्शन का उपयोग करता है फ़िल्टर.एक्सएमएल हम इस उत्तर को घटकों में "पार्स" करते हैं, इससे हमें आवश्यक डेटा निकालते हैं।

आइए एक उत्कृष्ट उदाहरण का उपयोग करते हुए इन कार्यों के संचालन को देखें - किसी भी मुद्रा की विनिमय दर को आयात करना जो हमें हमारे देश के सेंट्रल बैंक की वेबसाइट से एक निश्चित तिथि अंतराल के लिए चाहिए। हम निम्नलिखित निर्माण को रिक्त स्थान के रूप में उपयोग करेंगे:

यहाँ:

• पीली कोशिकाओं में हमारे लिए ब्याज की अवधि की शुरुआत और समाप्ति तिथियां होती हैं।
• नीले रंग में कमांड का उपयोग करके मुद्राओं की एक ड्रॉप-डाउन सूची होती है डेटा - सत्यापन - सूची (डेटा - सत्यापन - सूची).
• हरे रंग की कोशिकाओं में, हम क्वेरी स्ट्रिंग बनाने और सर्वर की प्रतिक्रिया प्राप्त करने के लिए अपने कार्यों का उपयोग करेंगे।
• दाईं ओर की तालिका मुद्रा कोड का संदर्भ है (हमें थोड़ी देर बाद इसकी आवश्यकता होगी)।
  

चलिए चलते हैं!

चरण 1. एक क्वेरी स्ट्रिंग बनाना

साइट से आवश्यक जानकारी प्राप्त करने के लिए, आपको इसे सही ढंग से पूछने की आवश्यकता है। हम www.cbr.ru पर जाते हैं और मुख्य पृष्ठ के पाद लेख में लिंक खोलते हैं। तकनीकी संसाधन'- XML का उपयोग करके डेटा प्राप्त करना (http://cbr.ru/Development/SXML/)। हम थोड़ा नीचे स्क्रॉल करते हैं और दूसरे उदाहरण (उदाहरण 2) में हमें जो चाहिए वह होगा - किसी दिए गए दिनांक अंतराल के लिए विनिमय दर प्राप्त करना:

जैसा कि आप उदाहरण से देख सकते हैं, क्वेरी स्ट्रिंग में प्रारंभ तिथियां होनी चाहिए (दिनांक_req1) और अंत (दिनांक_req2) हमारे लिए ब्याज की अवधि और मुद्रा कोड (वीएएल_एनएम_आरक्यू), जिसकी दर हम प्राप्त करना चाहते हैं। आप नीचे दी गई तालिका में मुख्य मुद्रा कोड पा सकते हैं:

सेंट्रल बैंक की वेबसाइट पर करेंसी कोड की पूरी गाइड भी उपलब्ध है - देखें http://cbr.ru/scripts/XML_val.asp?d=0

अब हम एक शीट पर एक सेल में एक क्वेरी स्ट्रिंग बनाएंगे:

• टेक्स्ट कॉन्सटेनेशन ऑपरेटर (&) को एक साथ रखने के लिए;
  
• विशेषताएं VPR (VLOOKUP)निर्देशिका में हमें जिस मुद्रा की आवश्यकता है उसका कोड खोजने के लिए;
  
• विशेषताएं पाठ (पाठ), जो दिए गए पैटर्न के अनुसार दिन-महीने-वर्ष के अनुसार तारीख को एक स्लैश के माध्यम से परिवर्तित करता है।

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

चरण 2. अनुरोध निष्पादित करें

अब हम फ़ंक्शन का उपयोग करते हैं वेब सेवा (वेब सेवा) उत्पन्न क्वेरी स्ट्रिंग के साथ एकमात्र तर्क के रूप में। उत्तर एक्सएमएल कोड की एक लंबी लाइन होगी (यदि आप इसे पूरी तरह से देखना चाहते हैं तो वर्ड रैप चालू करना और सेल आकार बढ़ाना बेहतर है):

चरण 3. उत्तर को पार्स करना

प्रतिक्रिया डेटा की संरचना को समझना आसान बनाने के लिए, ऑनलाइन एक्सएमएल पार्सर्स में से एक का उपयोग करना बेहतर है (उदाहरण के लिए, http://xpather.com/ या https://jsonformatter.org/xml-parser), जो एक्सएमएल कोड को नेत्रहीन रूप से प्रारूपित कर सकता है, इसमें इंडेंट जोड़ सकता है और रंग के साथ सिंटैक्स को हाइलाइट कर सकता है। तब सब कुछ बहुत स्पष्ट हो जाएगा:

अब आप स्पष्ट रूप से देख सकते हैं कि पाठ्यक्रम मान हमारे टैग द्वारा तैयार किए गए हैं ..., और तिथियाँ विशेषताएँ हैं तारीख टैग में .

उन्हें निकालने के लिए, शीट पर दस (या अधिक - यदि मार्जिन के साथ किया जाता है) के एक कॉलम का चयन करें (क्योंकि 10-दिन की तारीख अंतराल सेट किया गया था) और सूत्र बार में फ़ंक्शन दर्ज करें फ़िल्टर.एक्सएमएल (फ़िल्टरएक्सएमएल):

यहां, पहला तर्क सर्वर प्रतिक्रिया (बी8) के साथ एक सेल के लिए एक लिंक है, और दूसरा XPath में एक क्वेरी स्ट्रिंग है, एक विशेष भाषा जिसका उपयोग आवश्यक एक्सएमएल कोड अंशों तक पहुंचने और उन्हें निकालने के लिए किया जा सकता है। आप XPath भाषा के बारे में अधिक पढ़ सकते हैं, उदाहरण के लिए, यहाँ।

यह महत्वपूर्ण है कि सूत्र दर्ज करने के बाद, दबाएं नहीं दर्ज, और कीबोर्ड शॉर्टकट कंट्रोल+पाली+दर्ज, यानी इसे एक सरणी सूत्र के रूप में दर्ज करें (इसके चारों ओर घुंघराले ब्रेसिज़ स्वचालित रूप से जोड़ दिए जाएंगे)। यदि आपके पास Excel में डायनेमिक सरणियों के समर्थन के साथ Office 365 का नवीनतम संस्करण है, तो एक सरल दर्ज, और आपको पहले से खाली कक्षों का चयन करने की आवश्यकता नहीं है - फ़ंक्शन स्वयं उतनी ही कोशिकाओं को ले जाएगा जितनी इसकी आवश्यकता है।

तिथियां निकालने के लिए, हम वही करेंगे - हम आसन्न कॉलम में कई खाली कोशिकाओं का चयन करेंगे और एक ही फ़ंक्शन का उपयोग करेंगे, लेकिन एक अलग XPath क्वेरी के साथ, रिकॉर्ड टैग से दिनांक विशेषताओं के सभी मान प्राप्त करने के लिए:

=फ़िल्टर.एक्सएमएल(बी8;”//रिकॉर्ड/@दिनांक”)

अब भविष्य में, मूल सेल B2 और B3 में तिथियों को बदलते समय या सेल B3 की ड्रॉप-डाउन सूची में एक अलग मुद्रा का चयन करते समय, हमारी क्वेरी स्वचालित रूप से अपडेट हो जाएगी, नए डेटा के लिए सेंट्रल बैंक सर्वर का जिक्र करते हुए। मैन्युअल रूप से अपडेट को बाध्य करने के लिए, आप अतिरिक्त रूप से कीबोर्ड शॉर्टकट का उपयोग कर सकते हैं कंट्रोल+ऑल्ट+F9.

• Power Query के माध्यम से Excel में बिटकॉइन दर आयात करें
• एक्सेल के पुराने संस्करणों में इंटरनेट से विनिमय दरों को आयात करें