Wisselkoersen importeren van internet

Het importeren van de koers van een bepaalde valuta van internet met automatische updates is een veel voorkomende taak voor veel Microsoft Excel-gebruikers. Stel je voor dat je een prijslijst hebt die elke ochtend opnieuw moet worden berekend volgens de wisselkoers. Of de projectbegroting. Of de kosten van het contract, die moeten worden berekend aan de hand van de dollarkoers op de datum van het sluiten van het contract.

In dergelijke situaties kun je het probleem op verschillende manieren oplossen - het hangt allemaal af van welke versie van Excel je hebt geïnstalleerd en welke add-ons er bovenop zitten.

Methode 1: Een eenvoudig webverzoek voor de huidige wisselkoers

Deze methode is geschikt voor degenen die nog oude versies van Microsoft Office 2003-2007 op hun computer hebben staan. Het gebruikt geen add-ons of macro's van derden en werkt alleen op ingebouwde functies.

Klik op de knop Van het internet (Web) tab Data (Datum). In het venster dat verschijnt, in de regel Adres (Adres) voer de URL in van de site waarvan de informatie afkomstig is (bijvoorbeeld http://www.finmarket.ru/currency/rates/) en druk op de toets Enter.

Wanneer de pagina wordt geladen, verschijnen zwarte en gele pijlen op tabellen die Excel kan importeren. Als u op zo'n pijl klikt, wordt de tabel gemarkeerd om te importeren.

Wanneer alle benodigde tabellen zijn gemarkeerd, klikt u op de knop import (Importeren) onderaan het venster. Na enige tijd die nodig is om de gegevens te laden, verschijnt de inhoud van de gemarkeerde tabellen in de cellen op het blad:

Voor extra aanpassingen kunt u met de rechtermuisknop op een van deze cellen klikken en de opdracht in het contextmenu selecteren. Bereik eigenschappen (Databereik eigenschappen).In dit dialoogvenster is het desgewenst mogelijk om de updatefrequentie en andere parameters te configureren:

Aandelenkoersen, aangezien ze om de paar minuten veranderen, kunt u vaker bijwerken (selectievakje Ververs elke N min.), maar wisselkoersen is in de meeste gevallen voldoende om eenmaal per dag bij te werken (het selectievakje Update bij geopend bestand).

Merk op dat het volledige geïmporteerde gegevensbereik door Excel wordt behandeld als een enkele eenheid en een eigen naam krijgt, die te zien is in de Name Manager op het tabblad formule (Formules — Naam Manager).

Methode 2: Parametrische webquery om de wisselkoers voor een bepaald datumbereik te krijgen

Deze methode is een enigszins gemoderniseerde eerste optie en geeft de gebruiker de mogelijkheid om de wisselkoers van de gewenste valuta te ontvangen, niet alleen voor de huidige dag, maar ook voor elke andere datum of datuminterval van belang. Om dit te doen, moet ons webverzoek worden omgezet in een parametrische aanvraag, dwz er twee verduidelijkende parameters aan toevoegen (de code van de valuta die we nodig hebben en de huidige datum). Hiervoor doen we het volgende:

1. We creëren een webverzoek (zie methode 1) naar de pagina van de website van de Centrale Bank van Ons Land met het archief van cursussen: http://cbr.ru/currency_base/dynamics.aspx

2. Selecteer in het formulier aan de linkerkant de gewenste valuta en stel de start- en einddatum in:

3. Klik op de knop Gegevens ophalen: en na een paar seconden zien we een tabel met de koerswaarden die we nodig hebben voor een bepaald datuminterval. Blader door de resulterende tabel helemaal naar beneden en markeer deze voor import door op de zwarte en gele pijl in de linkerbenedenhoek van de webpagina te klikken (vraag alleen niet waarom deze pijl daar staat en niet naast de tabel - dit is een vraag voor de site designers).

Nu zijn we op zoek naar een knop met een diskette in de rechterbovenhoek van het venster Verzoek opslaan (Bewaar zoekopdracht) en sla het bestand met de parameters van ons verzoek op in een geschikte map onder een geschikte naam – bijvoorbeeld in Mijn documenten onder de naam cbr. iig.  Daarna kunnen het Web Query-venster en heel Excel voorlopig worden gesloten.

4. Open de map waarin u het verzoek hebt opgeslagen en zoek naar het verzoekbestand cbr. iqy, klik er dan met de rechtermuisknop op - Openen met - Kladblok (of selecteer het uit de lijst – meestal is het een bestand notepad.exe uit map C: Ramen). Na het openen van het aanvraagbestand in Kladblok, zou je zoiets als dit moeten zien:

Het meest waardevolle hier is de regel met het adres en de queryparameters erin, die we zullen vervangen - de code van de valuta die we nodig hebben (rood gemarkeerd) en de einddatum, die we zullen vervangen door die van vandaag (gemarkeerd in blauw). Bewerk de regel zorgvuldig om het volgende te krijgen:

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

Laat al het andere zoals het is, sla het bestand op en sluit het.

5. Maak een nieuw boek in Excel, open het blad waar we het archief van de Centrale Bank-tarieven willen importeren. Voer in een geschikte cel een formule in die ons de huidige datum geeft in tekstformaat voor vervanging van zoekopdrachten:

=TEKST(VANDAAG();”DD.MM.JJJJ”)

of in Engelse versie

=TEKST(VANDAAG(),»dd.mm.jjjj»)

Ergens in de buurt voeren we de code in van de valuta die we nodig hebben uit de tabel:

De vereiste code kan ook rechtstreeks op de website van de Centrale Bank in de queryreeks worden bekeken.

6. We laden de gegevens op het blad, met de aangemaakte cellen en het cbr.iqy-bestand als basis, dwz ga naar het tabblad Gegevens – Verbindingen – Andere zoeken (Gegevens — Bestaande verbindingen). Zoek en open het bestand in het gegevensbronselectievenster dat wordt geopend cbr. iqy. Voor het importeren zal Excel drie zaken met ons verduidelijken.

Ten eerste, waar de gegevenstabel moet worden geïmporteerd:

Ten tweede, waar u de valutacode vandaan kunt halen (u kunt het vakje aanvinken) Gebruik deze standaardwaarde (Gebruik deze waarde/referentie voor toekomstige vernieuwingen), zodat later elke keer dat deze cel niet wordt opgegeven tijdens updates en het selectievakje Automatisch bijwerken wanneer de celwaarde verandert (Automatisch vernieuwen wanneer celwaarde verandert):

Ten derde, uit welke cel de einddatum moet worden gehaald (u kunt hier ook beide vakjes aanvinken, zodat u deze parameters morgen niet handmatig hoeft in te stellen bij het bijwerken):

Klik OK, wacht een paar seconden en krijg een volledig archief van de wisselkoers van de gewenste valuta op het blad:

Net als bij de eerste methode, door met de rechtermuisknop op de geïmporteerde gegevens te klikken en de opdracht te selecteren Bereik eigenschappen (Databereik eigenschappen), kunt u de vernieuwingsfrequentie aanpassen Bij het openen van een bestand (Vernieuwen in bestand geopend). Als u dan toegang heeft tot internet, worden de gegevens elke dag automatisch bijgewerkt, dat wil zeggen dat de tabel automatisch wordt bijgewerkt met nieuwe gegevens.

Het is het gemakkelijkst om het tarief voor de gewenste datum uit onze tabel te halen met behulp van de functie VPR (VERT.ZOEKEN) – ben je er niet mee bekend, dan raad ik je ten zeerste aan dit te doen. Met een dergelijke formule kunt u bijvoorbeeld de dollarkoers voor 10 januari 2000 uit onze tabel selecteren:

of in het Engels =VERT.ZOEKEN(E5,cbr,3,1)

WAAR

• E5 – de cel met de opgegeven datum
  
• cbr – naam van het gegevensbereik (automatisch gegenereerd tijdens het importeren en meestal hetzelfde als de naam van het querybestand)
• 3 – het serienummer van de kolom in onze tabel, waar we de gegevens vandaan halen
• 1 – een argument dat een zoekactie bij benadering naar de functie VERT.ZOEKEN bevat, zodat u cursussen kunt vinden voor die tussenliggende datums die niet echt in kolom A staan ​​(de dichtstbijzijnde vorige datum en de cursus worden gevolgd). U kunt hier meer lezen over zoeken bij benadering met de functie VERT.ZOEKEN.

• Macro om de dollarkoers voor een bepaalde datum in de huidige cel te krijgen
• PLEX add-on-functie om de wisselkoers van de dollar, euro, hryvnia, pond sterling, enz. voor een bepaalde datum te krijgen
• Voeg een valutakoers in op een willekeurige datum in de PLEX-add-on