Ενημερωμένη συναλλαγματική ισοτιμία στο Excel

Έχω αναλύσει επανειλημμένα τρόπους εισαγωγής δεδομένων στο Excel από το Διαδίκτυο με επακόλουθη αυτόματη ενημέρωση. Συγκεκριμένα:

• Σε παλαιότερες εκδόσεις του Excel 2007-2013, αυτό θα μπορούσε να γίνει με ένα άμεσο αίτημα web.
• Από το 2010, αυτό μπορεί να γίνει πολύ εύκολα με το πρόσθετο Power Query.
  

Σε αυτές τις μεθόδους στις πιο πρόσφατες εκδόσεις του Microsoft Excel, μπορείτε τώρα να προσθέσετε μια άλλη - εισαγωγή δεδομένων από το Διαδίκτυο σε μορφή XML χρησιμοποιώντας ενσωματωμένες λειτουργίες.

Η XML (eXtensible Markup Language = Extensible Markup Language) είναι μια καθολική γλώσσα που έχει σχεδιαστεί για να περιγράφει κάθε είδους δεδομένα. Στην πραγματικότητα, είναι απλό κείμενο, αλλά με ειδικές ετικέτες που προστίθενται σε αυτό για τη σήμανση της δομής δεδομένων. Πολλοί ιστότοποι παρέχουν δωρεάν ροές των δεδομένων τους σε μορφή XML για λήψη από οποιονδήποτε. Στον ιστότοπο της Κεντρικής Τράπεζας της Χώρας μας (www.cbr.ru), συγκεκριμένα, με τη βοήθεια παρόμοιας τεχνολογίας, δίνονται δεδομένα για τις συναλλαγματικές ισοτιμίες διαφόρων νομισμάτων. Από τον ιστότοπο του Moscow Exchange (www.moex.com) μπορείτε να κατεβάσετε τιμές για μετοχές, ομόλογα και πολλές άλλες χρήσιμες πληροφορίες με τον ίδιο τρόπο.

Από την έκδοση 2013, το Excel έχει δύο λειτουργίες για την απευθείας φόρτωση δεδομένων XML από το Διαδίκτυο σε κελιά φύλλου εργασίας: ΥΠΗΡΕΣΙΑ ΙΣΤΟΣΕΛΙΔΑΣ (WEBSERVICE) и FILTER.XML (FILTERXML). Δουλεύουν σε ζευγάρια – πρώτα η συνάρτηση ΥΠΗΡΕΣΙΑ ΙΣΤΟΣΕΛΙΔΑΣ εκτελεί ένα αίτημα στον επιθυμητό ιστότοπο και επιστρέφει την απάντησή του σε μορφή XML και, στη συνέχεια, χρησιμοποιεί τη συνάρτηση FILTER.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 (VLOOKUP)για να βρούμε τον κωδικό του νομίσματος που χρειαζόμαστε στον κατάλογο.
  
• Χαρακτηριστικά ΚΕΙΜΕΝΟ (ΚΕΙΜΕΝΟ), το οποίο μετατρέπει την ημερομηνία σύμφωνα με το δεδομένο μοτίβο ημέρα-μήνας-έτος μέσω κάθετου.

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

Βήμα 2. Εκτελέστε το αίτημα

Τώρα χρησιμοποιούμε τη συνάρτηση ΥΠΗΡΕΣΙΑ ΙΣΤΟΣΕΛΙΔΑΣ (WEBSERVICE) με τη συμβολοσειρά ερωτήματος που δημιουργήθηκε ως μοναδικό όρισμα. Η απάντηση θα είναι μια μεγάλη σειρά κώδικα 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 από τις ετικέτες Εγγραφή:

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

Τώρα στο μέλλον, κατά την αλλαγή των ημερομηνιών στα αρχικά κελιά B2 και B3 ή κατά την επιλογή διαφορετικού νομίσματος στην αναπτυσσόμενη λίστα του κελιού B3, το ερώτημά μας θα ενημερώνεται αυτόματα, παραπέμποντας στον διακομιστή της Κεντρικής Τράπεζας για νέα δεδομένα. Για να επιβάλετε μια ενημέρωση με μη αυτόματο τρόπο, μπορείτε επιπλέον να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου Ctrl+άλλος+F9.

• Εισαγάγετε ποσοστό bitcoin στο Excel μέσω Power Query
• Εισαγάγετε συναλλαγματικές ισοτιμίες από το Διαδίκτυο σε παλαιότερες εκδόσεις του Excel