سعر الصرف المحدث في Excel

لقد قمت بتحليل طرق استيراد البيانات إلى Excel من الإنترنت بشكل متكرر مع التحديث التلقائي اللاحق. خاصه:

• في الإصدارات الأقدم من Excel 2007-2013 ، يمكن القيام بذلك عن طريق طلب ويب مباشر.
• بدءًا من عام 2010 ، يمكن القيام بذلك بسهولة تامة باستخدام الوظيفة الإضافية Power Query.
  

لهذه الطرق في أحدث إصدارات Microsoft Excel ، يمكنك الآن إضافة طريقة أخرى - استيراد البيانات من الإنترنت بتنسيق XML باستخدام الوظائف المضمنة.

XML (لغة الترميز الموسعة = لغة التوصيف الموسعة) هي لغة عالمية مصممة لوصف أي نوع من البيانات. في الواقع ، إنه نص عادي ، ولكن مع إضافة علامات خاصة إليه لترميز بنية البيانات. توفر العديد من المواقع تدفقات مجانية لبياناتها بتنسيق XML ليتمكن أي شخص من تنزيلها. على الموقع الإلكتروني للبنك المركزي لبلدنا (www.cbr.ru) ، على وجه الخصوص ، بمساعدة تقنية مماثلة ، يتم تقديم بيانات حول أسعار صرف العملات المختلفة. من موقع بورصة موسكو (www.moex.com) يمكنك تنزيل أسعار الأسهم والسندات والكثير من المعلومات المفيدة الأخرى بنفس الطريقة.

منذ إصدار 2013 ، يحتوي Excel على وظيفتين لتحميل بيانات XML مباشرة من الإنترنت إلى خلايا ورقة العمل: خدمة ويب (خدمة ويب) и مرشح. XML (فيلتركسمل). يعملون في أزواج - الوظيفة الأولى خدمة ويب ينفذ طلبًا إلى الموقع المطلوب ويعيد استجابته بتنسيق XML ، ثم يستخدم الوظيفة مرشح. XML نقوم "بتحليل" هذه الإجابة إلى مكونات ، واستخراج البيانات التي نحتاجها منها.

لنلقِ نظرة على تشغيل هذه الوظائف باستخدام مثال كلاسيكي - استيراد سعر الصرف لأي عملة نحتاجها لفترة زمنية معينة من موقع الويب للبنك المركزي لبلدنا. سوف نستخدم البناء التالي كفراغ:

هنا:

• تحتوي الخلايا الصفراء على تاريخي البدء والانتهاء للفترة التي تهمنا.
• يحتوي اللون الأزرق على قائمة منسدلة بالعملات باستخدام الأمر البيانات - التحقق - القائمة (البيانات - التحقق - القائمة).
• في الخلايا الخضراء ، سنستخدم وظائفنا لإنشاء سلسلة استعلام والحصول على استجابة الخادم.
• الجدول الموجود على اليمين هو إشارة إلى رموز العملات (سنحتاجها لاحقًا).
  

لنذهب!

الخطوة 1. تشكيل سلسلة استعلام

للحصول على المعلومات المطلوبة من الموقع ، عليك أن تطلبها بشكل صحيح. نذهب إلى www.cbr.ru ونفتح الرابط في تذييل الصفحة الرئيسية " الموارد الفنية'- الحصول على البيانات باستخدام XML (http://cbr.ru/development/SXML/). نقوم بالتمرير لأسفل قليلاً وفي المثال الثاني (مثال 2) سيكون هناك ما نحتاجه - الحصول على أسعار الصرف لفترة زمنية معينة:

كما ترى من المثال ، يجب أن تحتوي سلسلة الاستعلام على تواريخ البدء (date_req1) والنهايات (date_req2) من فترة الاهتمام بالنسبة لنا ورمز العملة (VAL_NM_RQ) ، المعدل الذي نريد الحصول عليه. يمكنك العثور على رموز العملات الرئيسية في الجدول أدناه:

يتوفر أيضًا دليل كامل لأكواد العملات على موقع البنك المركزي - راجع http://cbr.ru/scripts/XML_val.asp؟d=0

سنقوم الآن بتشكيل سلسلة استعلام في خلية على ورقة بها:

• عامل تسلسل النص (&) لتجميعه ؛
  
• المميزات VPR (البحث)للعثور على رمز العملة التي نحتاجها في الدليل ؛
  
• المميزات TEXT (نص)، والذي يحول التاريخ وفقًا للنمط المحدد يوم-شهر-سنة من خلال شرطة مائلة.

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

الخطوة 2. تنفيذ الطلب

الآن نستخدم الوظيفة خدمة ويب (خدمة ويب) مع سلسلة الاستعلام التي تم إنشاؤها باعتبارها الوسيطة الوحيدة. ستكون الإجابة عبارة عن سطر طويل من كود XML (من الأفضل تشغيل التفاف الكلمات وزيادة حجم الخلية إذا كنت تريد رؤيتها بالكامل):

الخطوة 3. تحليل الإجابة

لتسهيل فهم بنية بيانات الاستجابة ، من الأفضل استخدام أحد محللي XML عبر الإنترنت (على سبيل المثال ، http://xpather.com/ أو https://jsonformatter.org/xml-parser) ، والتي يمكنها تنسيق كود XML بشكل مرئي ، وإضافة مسافات بادئة إليها وإبراز بناء الجملة مع اللون. ثم يصبح كل شيء أكثر وضوحًا:

يمكنك الآن أن ترى بوضوح أن قيم الدورة التدريبية مؤطرة بواسطة علاماتنا ...، والتواريخ سمات التاريخ في العلامات .

لاستخراجها ، حدد عمودًا من عشرة (أو أكثر - إذا تم ذلك بهامش) خلايا فارغة على الورقة (لأنه تم تعيين فاصل زمني مدته 10 أيام) وأدخل الوظيفة في شريط الصيغة مرشح. XML (منقيXML):

هنا ، الوسيطة الأولى هي ارتباط إلى خلية ذات استجابة خادم (B8) ، والثانية عبارة عن سلسلة استعلام في XPath ، وهي لغة خاصة يمكن استخدامها للوصول إلى أجزاء كود XML الضرورية واستخراجها. يمكنك قراءة المزيد عن لغة XPath ، على سبيل المثال ، هنا.

من المهم عدم الضغط بعد إدخال الصيغة أدخل، واختصار لوحة المفاتيح CTRL+تغير+أدخل، على سبيل المثال ، أدخلها كصيغة مصفوفة (ستُضاف الأقواس المتعرجة حولها تلقائيًا). إذا كان لديك أحدث إصدار من Office 365 مع دعم للمصفوفات الديناميكية في Excel ، فسيكون ملف أدخل، ولست بحاجة إلى تحديد خلايا فارغة مسبقًا - ستستغرق الوظيفة نفسها أكبر عدد ممكن من الخلايا.

لاستخراج التواريخ ، سنفعل الشيء نفسه - سنختار عدة خلايا فارغة في العمود المجاور ونستخدم نفس الوظيفة ، ولكن مع استعلام XPath مختلف ، للحصول على جميع قيم سمات التاريخ من علامات السجل:

= FILTER.XML (B8 ؛ ”// سجل / @ التاريخ”)

الآن في المستقبل ، عند تغيير التواريخ في الخلايا الأصلية B2 و B3 أو اختيار عملة مختلفة في القائمة المنسدلة للخلية B3 ، سيتم تحديث استعلامنا تلقائيًا ، بالرجوع إلى خادم البنك المركزي للحصول على بيانات جديدة. لفرض التحديث يدويًا ، يمكنك أيضًا استخدام اختصار لوحة المفاتيح CTRL+قديم+F9.

• استيراد سعر البيتكوين إلى Excel عبر Power Query
• استيراد أسعار الصرف من الإنترنت في الإصدارات القديمة من Excel