שליפת שערי מטבע חוץ מאתר בנק ישראל אל אקסל

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

שלום וברכה, בנק ישראל חידש את האתר שלו, ובעקבות זאת, קיבלתי פניות רבות, לעדכן את הנוסחאות שפירסמתי בעבר לשליפת שער הדולר /היורו ועוד. במאמר הזה, אני אסביר שלב אחרי שלב איך שולפים מידע מבנק ישראל אל Excel. ההתייחסות תהיה למה שבנק ישראל מציע כיום, כלומר :

• ריבית בנק ישראל
• שערי מטבע חוץ

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

אם אתם מחפשים לשדרג את ניתוח הנתונים והדו"חות שלכם באופן מקצועי ?
אני ממליץ בחום לשקול את השירות שלנו של תותח Excel.
המומחיות שלנו באקסל תורמת להגשמת היעדים ולפתרון של בעיות מורכבות בצורה יעילה ומהירה.
עם מומחה Excel, אתם יכולים להיות בטוחים שתהליך העבודה שלכם יתנהל בצורה מקצועית.
ליצירת קשר לחצו כאן.

במדריך הזה אני אסביר על צורה פשוטה מאוד, 2 נוסחאות מאוד פשוטות, ובלי קוד VBA שמאפשרות לשלוף מידע מאתר בנק ישראל אל Excel.

אז בלי הקדמות מיותרות, ניגש ישר לנושא. רק 2 הערות חשובות לפני שמתחילים : 1. הנוסחאות האלו קיימות החל מאקסל 2013. 2. אם המונחים WebService ושאילתות Xpath מוכרים לך - אתה מוזמן לדלג לחלק השני בפוסט , על פונקציות WebService אם זה לא מוכר לך - הסעיפים הבאים מסבירים זאת בצורה מאוד קלה.

איך לשלוף שערי מטבע חוץ מבנק ישראל אל אקסל ?

מי שעבר על המדריכים שלי בעבר, לגבי שליפת מידע מ-api יודע שהשיטה הטובה ביותר לשלוף מידע מאתרים שונים, היא במידה והאתר מנגיש את המידע, בצורה של API.

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

• JSON
• XML

למי שהמושגים האלו לא אומרים לו כלום, אין מה לדאוג, כל מה שצריך לדעת מוסבר בהמשך.

באמצעות אקסל אפשר לשלוף ב-2 הצורות, אלא שלדעתי, כיוון שלשלוף מתוך JSON יצריך מאיתנו לעבור עם PowerQuery, אז הפעם אני אבחר בדרך הפשוטה יותר - לשלוף באמצעות נוסחאות מתוך ה-XML.

מה שאנחנו הולכים ללמוד במדריך הזה הוא :

1. למשוך שערי מט"ח מבנק ישראל ל-Excel

2. לתחקר את שערי המט"ח שמשכנו, ולשלוף מתוכם רק את הערך הרצוי (למשל, שער היורו).

וכמובן שהכל רלוונטי גם לריבית בנק ישראל.

איך מתקשרים עם שרת מתוך אקסל ?

כאשר אנו מעוניינים לשאוב מידע מאתר אינטרנט,

הצורה הנפוצה נקראת “שירות רשת” , או באנגלית Web Service.

מה זה Web Service ?

מדובר בתוכנה, שיושבת על השרת של אתר האינטרנט וממתינה שגולשים יגלשו לאתר.

ברגע שגולש מסוים - (את, אתה…) גולשים לכתובת הזו התוכנה הזו - מציגה לכם את הדף הרצוי.

עד כאן, אני מתאר לעצמי, לא חידשתי דבר לאף אחד. העניין הוא כזה - מידע לא חייב להיות מוחזר בתור אתר מעוצב.

אומנם, אנחנו, בני אדם, רגילים לראות בעיניים את המידע. אבל בעולם המחשבים, לא כל מידע שמוחזר חייב להיות מידע מעוצב, ופעמים רבות, מידע מוחזר בצורות שלא נראות הכי יפה לעין האנושית.

רוצה ליצור דוחות ותרשימים מתקדמים בעזרת Excel?
שירות של מומחה Excel יכול להציע לך פתרונות מותאמים לצרכים שלך.
צור קשר עכשיו

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

אבל…שניה לפני שנבין מה זה XML,

בואו נראה איך מושכים את המידע הזה, אל תוך Excel :

נוסחת WebService ב- Excel

ל- Excel יש נוסחה שיודעת להתחבר לעמוד אינטרנט ולשלוף את תוכן העמוד. הנוסחה נקראת WebService והיא מאוד פשוטה, יש לה רק פרמטר אחד - עמוד האינטרנט הרצוי (בתוך מרכאות) הנה דוגמא , עם הכתובת של שערי המט"ח בבנק ישראל :

=WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=USD&asXml=true")

מי שיעתיק את הנוסחה ל Excel

יקבל בתור תשובה טקסט ארוך שנראה כך

הטקסט הזה, מכיל בתוכו את כל שערי המטח הנכונים להיום. אך המידע הזה, שמור בצורת…XML.

במבט ראשון, זה אולי נראה מבהיל, אבל אחרי שמבינים את המבנה זה ממש הופך להיות פשוט.

הערה חשובה על רענון של נוסחת WebService : נוסחת WebService מתרעננת כמו כל נוסחה אחרת באקסל, זאת אומרת - רק כאשר הגיליון כולו מתרענן (בפתיחה, אלא אם הפסקתם את זה) , או כאשר בנוסחה יש הפניה לתא אחר, והתא ההוא עובר עריכה.

אז אני ממליץ בחום - לקשור את הנוסחה לתא שעושה תנאי “סתמי” מבוסס על Date , שתמיד יוצא “אמת”.

מה זה XML ?

לא נתעמק בזה, אבל נסביר  רק ש-XML זו צורה מסוימת לשמור מידע בקבצים . הנה דוגמא פשוטה לקובץ XML :

הקובץ הזה שומר מידע על אוסף הכלבים שלי כאשר אנו רואים שבאוסף יש רק כלב אחד והשם שלו הוא רמבו, הוא זכר מגזע פינצר.

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

כלומר אם נסתכל שוב פעם בקובץ שהצגתי, אפשר לראות בו 2 רמות:

• הרמה העליונה : אוסף הכלבים שלי
• הרמה הפנימית יותר :  כלב בודד

כמות הרמות לא מוגבלת, וכבר נתקלתי בעבר בקבצי XML אדירי מימדים, עם הרבה “מדרגות”.

למה חשוב המבנה של ה-XML ?

ל- Excel יש נוסחה שיודעת “לקבל תשובות” מתוך XML. ואם נדע קצת להשתמש בנוסחה הזו, נוכל לשלוף מתוך כל מיני אקס-אמ-אלים, מה שרק נרצה.

רוצה להפוך את עבודתך ב-Excel לפחות מסובכת ויותר יעילה?
מומחה Excel יכול להפוך תהליכים לאוטומטים ולבנות עבורך אקסלים יעילים.
צור קשר עכשיו

איך מתחקרים קבצי XML ?

אפשר לתשאל\לתחקר קובץ XML, נניח, את קובץ ה-XML של שערי המט"ח מבנק ישראל אפשר לשאול שאלות כמו :

• מה שער המטבע שנקרא “דולר” ?
• מה השינוי באחוזים לעומת השער הקודם ?
• מה שם המדינה שמטבע זה שייך לה ?

ואם נדע לשאול בצורה נכונה..אז נוכל גם לקבל תשובה שרצויה לנו. אז איך בעצם שואלים את ה-XML ?

תשאול של XML מבוצע באמצעות שפת שאילתות שנקראת XPATH

כאשר ישבה ועדה בארגון ה-W3C , וקבעה את תקן ה-XML, הן קבעו גם “שפה” שבה אפשר לשאול / לתחקר את ה-XML, והשפה הזו נקראת XPATH. באופן מעשי, מדובר רק בצורה מסוימת לכתוב שאלות (קישור באנגלית ).

לדוגמא, נחזור שוב פעם לדוגמא של “אוסף הכלבים שלי” אם נרצה לקבל את שם הכלב השאלה , או במילים אחרות שאילתת ה-Xpath , מנוסחת בצורה הבאה

//שם

כיצד לבצע שאילתות XPATH בתוך Excel ?

ובכן , גם לזה יש נוסחה שנקראת FILTERXML . הנוסחה הזו מקבלת 2 פרמטרים :

• את הטקסט של ה-XML
• את השאלה … או בשפה המקצועית, שאילתת XPATH

אז לצורך הדוגמא , העתקתי את טקסט ה-XML של אוסף הכלבים אל תוך תא ב Excel ורשמתי לייד את הנוסחה FILTERXML , עם השאילתא המתאימה :

=FILTERXML(M18,"//שם")

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

עכשיו, אחרי שאנחנו מבינים מהו WebService, יודעים להסתכל על XML, ומבינים שכדי לתחקר XML, אנחנו צריכים להשתמש בשאילתות Xpath הגיע הזמן סוף סוף למשוך את שערי המט"ח מבנק ישראל. זו הנוסחה עבור שער הדולר

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=USD&asXml=true"),"//CurrentExchangeRate")

הסבר הנוסחה :

• נוסחת ה-WEBSERVICE הפנימית, שולפת את ה-XML מתוך אתר בנק ישראל. היא מבקשת מה-API של בנק ישראל ספציפית את שער הדולר (USD) וספציפית בפורמט XML.
• נוסחת FILTERXML החיצונית, מבקשת רק את השדה שנקרא CurrentExchangeRate.

איך עובדת שאילתת XPATH ?

לפי הכללים של xpath , הסימן // ואחרי שם מסוים, שולף את כל הענפים בעץ  בעלי השם הזה, לא חשוב היכן הם מדורגים בעץ.

ולכן, בעצם אנחנו מבקשים את השדה CurrentExchangeRate מתוך ה-XML.

הערה למעוניינים בעדכון אוטומטי של השערים

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

אם נרצה שהנוסחה תתעדכן אוטומטית כל פעם שהאקסל נפתח \ כל יום וכדומה , נצטרך לגרום לנוסחה להתעדכן.

כמובן, שהשיטה הידנית היא פשוט לגשת לשורת הנוסחאות, וללחוץ שוב ENTER על הנוסחה, אבל אנחנו מעוניינים להימנע מזה. ולכן, אתן 2 פתרונות, כל אחד מיועד למקרה שונה :

• למקרה בו אנחנו מעוניינים שהנוסחה תתעדכן כל פעם שפותחים את האקסל. אז השיטה היא להוסיף לסוף הכתובת של ה- WEBSERVICE פרמטר “דמה” שלא קיים באמת, ושהאתר של בנק ישראל פשוט יתעלם ממנו. ואל הפרמטר הזה אנחנו “נזריק” את השעה של עכשיו - מתוך נוסחת אקסל. וזה יגרום לאקסל לרענן את הנוסחה בכל פעם.

דוגמא: שלב ראשון : בתא I32 שמתי נוסחה שנותנת את הזמן הנוכחי

 =NOW()

שלב שני : בנוסחה הרלוונטית, הוספתי את החלק של &t , שימו לב לחלק הזה בסוף נוסחת ה- WEBSERVICE ולחיבור שעשיתי עם תא I32

ולמעוניינים : זוהי הנוסחה להעתקה

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=USD&asXml=true&t=" &  I32),"//CurrentExchangeRate")

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

נוסחאות לשליפת שערי מטבע חוץ מאתר בנק ישראל אל אקסל

איך לשלוף את שער הדולר מאתר בנק ישראל אל אקסל ?

השתמשו בנוסחה :

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=USD&asXml=true"),"//CurrentExchangeRate")

איך לשלוף את שער היורו מאתר בנק ישראל אל אקסל ?

השתמשו בנוסחה :

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=EUR&asXml=true"),"//CurrentExchangeRate")

איך לשלוף את שער הליש"ט GBP מאתר בנק ישראל אל אקסל ?

השתמשו בנוסחה :

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=GBP&asXml=true"),"//CurrentExchangeRate")

איך לשלוף את שער היין היפני JPY מאתר בנק ישראל אל אקסל ?

השתמשו בנוסחה :

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=JPY&asXml=true"),"//CurrentExchangeRate")

איך לשלוף ריבית בנק ישראל אל אקסל ?

השתמשו בנוסחה :

=FILTERXML(WEBSERVICE("https://Boi.org.il/PublicApi/GetInterest?asXml=true"),"//CurrentInterest")

איך לשלוף את אחוז השינוי של שער הדולר מאתר בנק ישראל אל אקסל ?

• בנוסחה הזו מוצג כיצד לשלוף את אחוז השינוי
• על מנת לשנות למטבעות אחרים, החליפו את הקטע של “USD” בסימול המטבע המתאים
• על מנת להציג את התוצאה בתור אחוז - עצבו את התא כ"אחוז"

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=USD&asXml=true"),"//CurrentChange")

איך לשלוף את תאריך השינוי האחרון של שער הדולר אל אקסל ?

• בדוגמא הזו מוצג כיצד לשלוף את התאריך האחרון שבו נקבע שער מטבע חוץ יציג לדולר
• שימו לב, שכדי להציג את המידע בצורה תקינה, יש צורך לעצב את התא בתוך “תאריך” באקסל (לשונית “בית” > בחור בסוג התא “תאריך”)

=FILTERXML(WEBSERVICE("https://boi.org.il/PublicApi/GetExchangeRate?key=USD&asXml=true"),"//LastUpdate")

אם אתם מחפשים לשדרג את ניתוח הנתונים והדו"חות שלכם באופן מקצועי ?
אני ממליץ בחום לשקול את השירות שלנו של תותח Excel.
המומחיות שלנו באקסל תורמת להגשמת היעדים ולפתרון של בעיות מורכבות בצורה יעילה ומהירה.
עם מומחה Excel, אתם יכולים להיות בטוחים שתהליך העבודה שלכם יתנהל בצורה מקצועית.
ליצירת קשר לחצו כאן.