Stock Exchange

Adatok importálása weblapról

Egy pénzügyi területen tevékenykedő szakember gyakran szembesülhet azzal, hogy saját kimutatásainak frissítéséhez rendszeresen adatokat kell beszereznie valamilyen webes felületről: legyen az egy tőzsdeindex, részvényárfolyam, vagy banki-, jegybanki devizaárfolyam. Amennyiben az igényelt adat eleve egy Excel fájlként van a weblapra feltöltve, akkor a megnyitás automatizálása nem bonyolultabb művelet, mintha a fájl a merevlemezünkön lenne. Ennél egy lépéssel bonyolultabb, ha a kívánt adat (tábla) a weblap HTML kódjába van beágyazva – ennek kinyeréséről lesz szó a továbbiakban.

Példánkban egy, a pénzügyi területen dolgozók körében talán leggyakoribb tevékenység automatizálását tekintjük át: egy időszak napi Euró árfolyamainak letöltését a Magyar Nemzeti Bank holnapjáról.

Előkészítés

Első lépésként a böngészővel nyissuk meg azt az oldalt, ahonnan a táblát importálni szeretnénk. A példánkban az MNB árfolyamlekérdező oldaláról indulunk (http://www.mnb.hu/arfolyam-lekerdezes). Az oldalon válasszuk ki az Eurót, adjuk meg a lekérdezendő intervallum kezdetét (a példánkban 2016.01.01.), az intervallum vége dátumának pedig egy kellően távoli dátumot ahhoz, hogy nyugdíjas korunkig ne kelljen változtatni a lekérdezésen (pl. 2100.12.31).

Az “Árfolyam információk lekérése” gomb megnyomásával kapunk egy táblát, ami 2016. év elejétől a mai napig tartalmazza a napi Euró árfolyamokat.

Kattintsunk jobb egér gombbal a táblán bárhová. A megjelenő lehetőségek közül válasszuk ki az “Exportálás a Microsoft Excel programba” műveletet.

Néhány pillanattal később egy új Excel munkafüzetben megnyílik az árfolyamtábla. Sok esetben néhány további beállítással, de makrók használata nélkül ennyi is elég lehet. Az Excelben az adattábla bármelyik cellájára kattintva jobb egérgombbal előhívható a “Data Range Properties…” párbeszédablak (vagy a Ribbonról a Data > Connections > Properties útvonalon). Itt többek között beállítható, hogy a munkafüzet megnyitásakor, és/vagy bizonyos időközönként frissítse az Excel a táblát.

Milyen esetekben lehet mégis szükség makrókkal való kiegészítésre?

Egyrészt, ha az Excel biztonsági beállításainál (Trust Center) nincs engedélyezve a külső kapcsolatok automatikus, kérdés nélküli frissítése, akkor az adattáblára vonatkozóan hiába állítjuk be, hogy a munkafüzet megnyitásakor automatikusan frissítse az adatokat, az nem fog megtörténni, a frissítés kézi indításakor is rákérdez a program, hogy biztosan akarjuk-e a frissítést. Ezt a kézi frissítést lehet egy rövid szkripttel automatizálni, hogy a frissítés a fájl megnyitásakor automatikusan lefusson.

Másik probléma, amibe belefuthatunk, hogy a táblában szereplő adatok formátuma nem megfelelő: a példánkban a dátumok szövegként szerepelnek, amit az Excel nem tud dátumként értelmezni. Makrók segítségével a formázási problémák pillanatok alatt orvosolhatóak.

Automatikus frissítés a munkafüzet megnyitásakor

Ahhoz, hogy egy utasítássor automatikusan lefusson a munkafüzet megnyitásakor, a ThisWorkbook munkafüzet modulban a Workbook_Open() eljárásba írjuk az utasításainkat. A lenti példában feltételezzük, hogy a munkafüzetünk csak egy munkalapot tartalmaz, annak A1 cellájában kezdődik az importált tábla, és csak ezt az egy importált táblát tartalmazza a munkalap:

Private Sub Workbook_Open()

Application.EnableEvents = False
    Activesheet.QueryTables(1).Refresh BackgroundQuery:=False

    Frissites

Application.EnableEvents = True
End Sub

Az Activesheet.QueryTables(1).Refresh metódussal utasítjuk az Excelt, hogy az aktív munkalapon szereplő 1-es indexű QueryTable-t (külső adatforrásra mutató táblát) frissítse. A BackgroundQuery tulajdonsággal állíthatjuk be, hogy a frissítési művelet befejezéséig függessze fel a további műveletek végrehajtását (False érték) vagy a háttérben futtassa (True érték). Nekünk jelen esetben a False értékre van szükségünk, hogy a további (például formázási) műveletekkel megvárjuk, amíg a frissítés befejeződik.

Dátumok átalakítása

Az eljárással végiglépdelünk a dátum oszlop egyes celláin, ha szöveges formátumban van a dátum (vagyis a hónap betűvel van kiírva), akkor azt átalakítjuk olyan adattá, amit az Excel is dátumként ismer fel, majd meg is formázzuk a kívánt dátumformátumra.

Dim Hiba As Boolean

Public Sub Frissites()

Dim i As Long
Dim Datumszoveg As String

Application.EnableEvents = False
   For i = 4 To Range("A1").CurrentRegion.Rows.Count
      Hiba = False
      Datumszoveg = Cells(i, 1)
      Cells(i, 1) = DatumAtalakitas(Datumszoveg)
      With Cells(i, 1)
         If Hiba = True Then
            .Value2 = Datumszoveg
         Else
            On Error Resume Next
            .NumberFormat = "yyyy.mm.dd"
            .NumberFormat = "éééé.hh.nn"
            On Error GoTo 0
         End If
      End With
   Next i

Application.EnableEvents = True
End Sub

Látható, hogy az eljárás “lelke” a DatumAtalakitas nevű függvény, ez végzi el ugyanis a cella átalakítását. Lássuk a függvényt:

Function DatumAtalakitas(Datumszoveg As String) As Date

Dim ev As Integer
Dim honapszoveg As String
Dim honapszam As Integer
Dim nap As Integer

   ev = Left(Datumszoveg, 4)
   honapszoveg = Mid(Datumszoveg, InStr(1, Datumszoveg, " ") + 1, _
      InStr(7, Datumszoveg, " ") - InStr(1, Datumszoveg, " ") - 1)
   Select Case honapszoveg
      Case "január"
         honapszam = 1
      Case "február"
         honapszam = 2
      Case "március"
         honapszam = 3
      Case "április"
         honapszam = 4
      Case "május"
         honapszam = 5
      Case "június"
         honapszam = 6
      Case "július"
         honapszam = 7
      Case "augusztus"
         honapszam = 8
      Case "szeptember"
         honapszam = 9
      Case "október"
         honapszam = 10
      Case "november"
         honapszam = 11
      Case "december"
         honapszam = 12
      Case Else
         honapszam = 0
   End Select

   If Not honapszam = 0 Then
      nap = Mid(Datumszoveg, InStr(7, Datumszoveg, " ") + 1, _
         InStr(7, Datumszoveg, ".") - InStr(7, Datumszoveg, " ") - 1)
      DatumAtalakitas = DateSerial(ev, honapszam, nap)
   Else
      Hiba = True
   End If

End Function

Az eljárásban szövegfüggvényekkel azonosítjuk a dátum összetevőit (év, hónap, nap); a legérdekesebb talán a hónap azonosítása. A Mid és InStr függvények egybeágyazásával a két szóköz közötti karaktersorozatot emeli ki, ami nem más mint a hónap neve. Ezután egy Select Case utasítással rendeljük a magyar hónapnevekhez az adott hónap sorszámát.

A függvényben elhelyeztünk egy “Hiba” elnevezésű változót. Ennek feladata, hogy kiszűrje azokat az eseteket, amikor valami hiba csúszik a rendszerbe és az adott cellában nem szerepel egyik hónap neve sem (vagy elgépelés miatt, vagy azért, mert már eleve dátumformátumban van a cella). Jelen példánkban ilyen jellegű hiba esetén a cellát változatlanul hagyjuk.

Frissítés manuálisan

Ha már nyitva van a munkafüzetünk, az Excel lehetőséget ad arra is, hogy kézzel frissítsük az adattáblánkat (az opció előhívható a jobb egérgomb menüjéből, vagy a Ribbonról a Data > Connections útvonalon). Még egy rövid szkriptre szükségünk van ahhoz, hogy a fentiekben bemutatott makró az adattábla kézi frissítésekor is lefusson. Ehhez a Sheet1 munkalap modul Worksheet_Change eseményéhez írjuk a következőket:

Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False

      Frissites

   Application.EnableEvents = True

End Sub

Látható, hogy az összes eljárásban az eljárás elején kikapcsoljuk az események figyelését (Application.EnableEvents = False) az eljárás végén pedig vissza. Erre azért van szükségünk, hogy ne kerüljenek az eljárások végtelen ciklusba: az adattábla frissítésével elindítjuk a “Frissites” nevű eljárást, amit az Excel esemény figyelője a munkalap változásaként értelmez, ezért lefut a Worksheet_Change eljárás, ami újra elindítja a “Frissites” nevű eljárást, ami miatt újra lefut a Worksheet_Change eljárás… és így tovább.

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.