Uvoz tečajeva s interneta

Uvoz tečaja određene valute s interneta s automatskim ažuriranjem vrlo je čest zadatak za mnoge korisnike programa Microsoft Excel. Zamislite da imate cjenik koji se svako jutro mora preračunavati prema tečaju. Ili proračun projekta. Ili trošak ugovora, koji se mora izračunati prema tečaju dolara na dan sklapanja ugovora.

U takvim situacijama problem možete riješiti na različite načine – sve ovisi o tome koju verziju Excela imate instaliranu i koji su dodaci na njoj.

Metoda 1: Jednostavan web zahtjev za trenutni tečaj

Ova metoda je prikladna za one koji još uvijek imaju stare verzije Microsoft Office 2003-2007 na svom računalu. Ne koristi dodatke ili makronaredbe trećih strana i radi samo na ugrađenim funkcijama.

Pritisnite gumb S interneta (Mreža) kartica Datum (Datum). U prozoru koji se pojavi, u liniji Adresa (Adresa) unesite URL stranice s koje će se preuzeti podaci (na primjer, http://www.finmarket.ru/currency/rates/) i pritisnite tipku ući.

Kada se stranica učita, crne i žute strelice pojavit će se na tablicama koje Excel može uvesti. Klikom na takvu strelicu označava se tablica za uvoz.

Kada su sve potrebne tablice označene, kliknite gumb uvoz (Uvoz) na dnu prozora. Nakon nekog vremena potrebnog za učitavanje podataka, sadržaj označenih tablica pojavit će se u ćelijama na listu:

Za dodatnu prilagodbu možete desnom tipkom miša kliknuti bilo koju od ovih ćelija i odabrati naredbu iz kontekstnog izbornika. Svojstva raspona (Svojstva raspona podataka).U ovom dijaloškom okviru, po želji, moguće je konfigurirati učestalost ažuriranja i druge parametre:

Kotacije dionica, budući da se mijenjaju svakih nekoliko minuta, možete češće ažurirati (potvrdni okvir Osvježi svakih N min.), no tečajeve je u većini slučajeva dovoljno ažurirati jednom dnevno (potvrdni okvir Ažuriranje datoteke otvoreno).

Imajte na umu da cijeli uvezeni raspon podataka Excel tretira kao jednu jedinicu i daje joj vlastiti naziv, koji se može vidjeti u Upravitelju naziva na kartici formula (Formule — Upravitelj naziva).

Metoda 2: Parametarski web upit za dobivanje tečaja za određeni datumski raspon

Ova metoda je malo modernizirana prva opcija i daje korisniku mogućnost dobivanja tečaja željene valute ne samo za tekući dan, već i za bilo koji drugi datum ili datumski interval od interesa. Da bismo to učinili, naš web zahtjev moramo pretvoriti u parametarski, tj. dodati mu dva parametra za pojašnjenje (šifru valute koja nam je potrebna i trenutni datum). Da bismo to učinili, činimo sljedeće:

1. Kreiramo web zahtjev (vidi metodu 1) na stranicu web stranice Središnje banke naše zemlje s arhivom tečajeva: http://cbr.ru/currency_base/dynamics.aspx

2. U obrascu s lijeve strane odaberite željenu valutu i postavite datum početka i završetka:

3. Kliknite gumb Da biste dobili podatke i nakon nekoliko sekundi vidimo tablicu s vrijednostima tečaja koje su nam potrebne za određeni datumski interval. Pomaknite dobivenu tablicu do kraja i označite je za uvoz klikom na crno-žutu strelicu u donjem lijevom kutu web stranice (samo nemojte pitati zašto je ova strelica tu, a ne pokraj tablice – ovo je pitanje za dizajnere stranice).

Sada tražimo gumb s disketom u gornjem desnom kutu prozora Spremi zahtjev (Spremi upit) i spremite datoteku s parametrima našeg zahtjeva u bilo koju prikladnu mapu pod bilo kojim prikladnim imenom - na primjer, u Moje dokumente pod imenom cbr. iqy.  Nakon toga, prozor Web Query i cijeli Excel mogu se za sada zatvoriti.

4. Otvorite mapu u kojoj ste spremili zahtjev i potražite datoteku zahtjeva cbr. iqy, zatim desnom tipkom miša kliknite na njega – Otvorite pomoću – Notepad (ili je odaberite s popisa – obično je to datoteka Notepad.exe iz mape C:Windows). Nakon otvaranja datoteke zahtjeva u Notepadu, trebali biste vidjeti nešto poput ovoga:

Ovdje je najvrjednija linija s adresom i parametrima upita u njoj, koje ćemo zamijeniti – šifra valute koja nam je potrebna (označeno crveno) i datum završetka koji ćemo zamijeniti današnjim (označeno u plava). Pažljivo uredite liniju kako biste dobili sljedeće:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Šifra valute"]&date_req1=01.01.2000&r1=1&date_req2=["Datum"]&rt=1&mode=1

Ostalo ostavite kako jest, spremite i zatvorite datoteku.

5. Napravimo novu knjigu u Excelu, otvorimo list na koji želimo uvesti arhivu tečajeva Centralne banke. U bilo koju prikladnu ćeliju unesite formulu koja će nam dati trenutni datum u tekstualnom formatu za zamjenu upita:

=TEKST(DANAS();”DD.MM.GGGG”)

ili u engleskoj verziji

=TEKST(DANAS(),»dd.mm.gggg»)

Negdje u blizini upisujemo šifru valute koja nam je potrebna iz tablice:

Potreban kod također se može pogledati u nizu upita izravno na web stranici Središnje banke.

6. Učitavamo podatke na list, koristeći kreirane ćelije i datoteku cbr.iqy kao osnovu, tj. idemo na tab. Podaci – Veze – Pronađite druge (Podaci — Postojeće veze). U prozoru za odabir izvora podataka koji se otvori pronađite i otvorite datoteku cbr. iqy. Excel će prije uvoza s nama razjasniti tri stvari.

Prvo, gdje uvesti podatkovnu tablicu:

Drugo, odakle dobiti šifru valute (možete potvrditi okvir Koristite ovu zadanu vrijednost (Koristite ovu vrijednost/referencu za buduća osvježavanja), tako da kasnije svaki put kada ova ćelija nije navedena tijekom ažuriranja i potvrdnog okvira Automatski ažuriraj kada se promijeni vrijednost ćelije (Automatski osvježi kada se promijeni vrijednost ćelije):

Treće, iz koje ćelije uzeti datum završetka (također ovdje možete označiti oba polja tako da sutra ne morate ručno postavljati ove parametre prilikom ažuriranja):

Kliknite OK, pričekajte par sekundi i dobit ćete kompletnu arhivu tečaja željene valute na listu:

Kao i kod prve metode, desnim klikom na uvezene podatke i odabirom naredbe Svojstva raspona (Svojstva raspona podataka), možete prilagoditi brzinu osvježavanja Prilikom otvaranja datoteke (Osvježi pri otvaranju datoteke). Zatim, ako imate pristup Internetu, podaci će se automatski ažurirati svaki dan, tj. Tablica će se automatski ažurirati novim podacima.

Tečaj za željeni datum najlakše je izdvojiti iz naše tablice pomoću funkcije VPR (VLOOKUP) – ako niste upoznati s tim, onda vam toplo savjetujem da to učinite. S takvom formulom, na primjer, možete odabrati tečaj dolara za 10. siječnja 2000. iz naše tablice:

ili na engleskom =VLOOKUP(E5,cbr,3,1)

gdje

• E5 – ćelija koja sadrži navedeni datum
  
• CBR – naziv raspona podataka (automatski generiran tijekom uvoza i obično isti kao naziv datoteke upita)
• 3 – redni broj stupca u našoj tablici iz kojeg dobivamo podatke
• 1 – argument koji uključuje približnu pretragu funkcije VLOOKUP kako biste mogli pronaći tečajeve za one međudatume koji zapravo nisu prisutni u stupcu A (uzet će se najbliži prethodni datum i njegov tečaj). Više o približnom pretraživanju pomoću funkcije VLOOKUP možete pročitati ovdje.

• Makro za dobivanje tečaja dolara za određeni datum u trenutnoj ćeliji
• PLEX dodatna funkcija za dobivanje tečaja dolara, eura, grivne, funte sterlinga itd. za bilo koji datum
• Unesite bilo koji tečaj valute na bilo koji datum u PLEX dodatak