שער חליפין מעודכן באקסל

ניתחתי שוב ושוב דרכים לייבא נתונים ל-Excel מהאינטרנט עם עדכון אוטומטי שלאחר מכן. באופן מיוחד:

• בגירסאות ישנות יותר של Excel 2007-2013, ניתן לעשות זאת באמצעות בקשת אינטרנט ישירה.
• החל משנת 2010, ניתן לעשות זאת בנוחות רבה עם תוסף Power Query.
  

לשיטות אלו בגרסאות האחרונות של Microsoft Excel, כעת תוכל להוסיף עוד אחת - ייבוא ​​נתונים מהאינטרנט בפורמט XML באמצעות פונקציות מובנות.

XML (eXtensible Markup Language = Extensible Markup Language) היא שפה אוניברסלית שנועדה לתאר כל סוג של נתונים. למעשה, זהו טקסט רגיל, אך עם תגיות מיוחדות שנוספו לו כדי לסמן את מבנה הנתונים. אתרים רבים מספקים זרמים בחינם של הנתונים שלהם בפורמט XML להורדה של כל אחד. באתר האינטרנט של הבנק המרכזי של ארצנו (www.cbr.ru), בפרט, בעזרת טכנולוגיה דומה, ניתן נתונים על שערי החליפין של מטבעות שונים. מאתר הבורסה של מוסקבה (www.moex.com) תוכלו להוריד באותו אופן ציטוטים של מניות, אג"ח והרבה מידע שימושי אחר.

מאז גרסה 2013, ל- Excel שתי פונקציות לטעינה ישירה של נתוני XML מהאינטרנט לתאי גליון עבודה: שירות רשת (שירות רשת) и FILTER.XML (FILTERXML). הם עובדים בזוגות - ראשית הפונקציה שירות רשת מבצע בקשה לאתר הרצוי ומחזיר את תגובתו בפורמט XML, ולאחר מכן באמצעות הפונקציה FILTER.XML אנו "מנתחים" את התשובה הזו לרכיבים, מחלצים ממנה את הנתונים הדרושים לנו.

הבה נבחן את פעולתן של פונקציות אלו באמצעות דוגמה קלאסית - ייבוא ​​שער החליפין של כל מטבע שאנו צריכים עבור מרווח תאריכים נתון מאתר האינטרנט של הבנק המרכזי של ארצנו. נשתמש במבנה הבא בתור ריק:

כאן:

• התאים הצהובים מכילים את תאריכי ההתחלה והסיום של התקופה המעניינת אותנו.
• לכחול יש רשימה נפתחת של מטבעות באמצעות הפקודה נתונים – אימות – רשימה (נתונים - אימות - רשימה).
• בתאים הירוקים, נשתמש בפונקציות שלנו כדי ליצור מחרוזת שאילתה ולקבל את תגובת השרת.
• הטבלה מימין היא הפניה לקודי מטבע (נצטרך אותה קצת מאוחר יותר).
  

בוא נלך!

שלב 1. יצירת מחרוזת שאילתה

כדי לקבל את המידע הנדרש מהאתר, עליך לשאול אותו בצורה נכונה. אנחנו עוברים אל www.cbr.ru ופותחים את הקישור בכותרת התחתונה של העמוד הראשי' משאבים טכניים'- קבלת נתונים באמצעות XML (http://cbr.ru/development/SXML/). אנחנו גוללים קצת למטה ובדוגמה השנייה (דוגמה 2) יהיה מה שאנחנו צריכים - קבלת שערי החליפין עבור מרווח תאריכים נתון:

כפי שניתן לראות מהדוגמה, מחרוזת השאילתה חייבת להכיל תאריכי התחלה (תאריך_דרישה1) וסופים (תאריך_דרישה2) של התקופה המעניינת אותנו וקוד המטבע (VAL_NM_RQ), את השיעור שאנו רוצים לקבל. אתה יכול למצוא את קודי המטבע העיקריים בטבלה למטה:

מדריך מלא לקודי מטבע זמין גם באתר הבנק המרכזי - ראה 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. בצע את הבקשה

כעת אנו משתמשים בפונקציה שירות רשת (שירות רשת) עם מחרוזת השאילתה שנוצרה כארגומנט היחיד. התשובה תהיה שורה ארוכה של קוד XML (עדיף להפעיל גלישת מילים ולהגדיל את גודל התא אם תרצו לראות אותו בשלמותו):

שלב 3. ניתוח התשובה

כדי להקל על הבנת המבנה של נתוני התגובה, עדיף להשתמש באחד ממנתחי ה-XML המקוונים (לדוגמה, http://xpather.com/ או https://jsonformatter.org/xml-parser), אשר יכול לעצב באופן ויזואלי קוד XML, להוסיף לו הזחות ולהדגיש את התחביר עם צבע. ואז הכל יתברר הרבה יותר:

עכשיו אתה יכול לראות בבירור שערכי הקורס ממוסגרים על ידי התגים שלנו ...ותאריכים הם תכונות תַאֲרִיך בתגים .

כדי לחלץ אותם, בחר עמודה של עשרה (או יותר - אם נעשה עם שוליים) תאים ריקים בגיליון (מכיוון שהוגדר מרווח תאריכים של 10 ימים) והזן את הפונקציה בשורת הנוסחאות FILTER.XML (לְסַנֵןXML):

כאן, הארגומנט הראשון הוא קישור לתא עם תגובת שרת (B8), והשני הוא מחרוזת שאילתה ב-XPath, שפה מיוחדת שניתן להשתמש בה כדי לגשת לשברי קוד ה-XML הדרושים ולחלץ אותם. אתה יכול לקרוא עוד על שפת XPath, למשל, כאן.

חשוב שלאחר הזנת הנוסחה אל תלחץ זן, ואת קיצור המקלדת Ctrl+משמרת+זן, כלומר הזינו אותו כנוסחת מערך (הסוגרים המתולתלים סביבו יתווספו אוטומטית). אם יש לך את הגרסה העדכנית ביותר של Office 365 עם תמיכה במערכים דינמיים ב-Excel, אז פשוט זן, ואתה לא צריך לבחור תאים ריקים מראש - הפונקציה עצמה תיקח כמה תאים שהיא צריכה.

כדי לחלץ תאריכים, נעשה את אותו הדבר - נבחר מספר תאים ריקים בעמודה הסמוכה ונשתמש באותה פונקציה, אך עם שאילתת XPath שונה, כדי לקבל את כל הערכים של תכונות ה-Date מתגיות ה-Record:

=FILTER.XML(B8;"//Record/@Date")

כעת בעתיד, בעת שינוי התאריכים בתאים המקוריים B2 ו-B3 או בחירת מטבע אחר ברשימה הנפתחת של תא B3, השאילתה שלנו תתעדכן אוטומטית, תוך התייחסות לשרת הבנק המרכזי לנתונים חדשים. כדי לאלץ עדכון ידני, אתה יכול בנוסף להשתמש בקיצור המקלדת Ctrl+אחר+F9.

• ייבוא ​​שער ביטקוין לאקסל באמצעות Power Query
• ייבוא ​​שערי חליפין מהאינטרנט בגרסאות ישנות יותר של אקסל