Jak do Excelu načítat data z registru ARES

Jak do Excelu načítat data z registru ARES a co to ARES vůbec je?

ARES (administrativní registr ekonomických subjektů) je informační systém Ministerstva financí, který umožňuje vyhledávání nad aktivními ekonomickými subjekty registrovanými v České republice. Není kdovíjak sofistikovaný, neslouží ani ke sběru kontaktních údajů pro marketingovou kampaň, nicméně běžnému smrtelníkovi může poskytnout informace třeba o adrese subjektu nebo IČO. Doporučuji přečíst si podmínky provozu a vyzkoušet hledání.

Načítání informací ze systému ARES

ARES nabízí několik zdrojů informací a forem zobrazených údajů. Mezi ty nejsnáze zpracovatelné patří HTML a XML. Pokud si přejete mít „pár XML souborů“ offline, pak ARES nabízí otevřená data (460MB ZIP ke stažení, uvnitř 1,7GB TAR, po rozbalení cca 5,7 GB dat, odhadem statisíce souborů). Raději to nezkoušejte, ještě nikdy jsem neviděl tak vycukaný Total Commander :-)

Mějme zkušební XML odkaz http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_std.cgi?ico=03418197, který pro IČO 03418197 (viz parametr v adrese) vrátí základní informace o společnosti Amazon Czech Republic.

Pro účely importu XML dat do Excelu kupodivu primárně nesloužila karta Data, ale karta Vývojář, a ta je dostupná dodnes. Postup zpracování ukazuje obrázek.

Excel - import dat XML prostřednictvím karty Vývojář — Excel – import dat XML prostřednictvím karty Vývojář

Problém je, že změna parametru IČO v adrese pro potřeby získání informací o další firmě je dle mého názoru prakticky nemožná (nerozpoznaný typ připojení, parametr je v něm schován, XML mapu nelze exportovat).

A co se tomu podívat na zoubek přes VBA? Inu, kolekci XMLMaps jsem pořádně neznal, takže přišel na řadu Záznamník maker. Ten nám předloží zhruba něco takového:

1
2
3
4
5
6
7
8
9
10
11

Sub NacistXMLData()

ActiveWorkbook.XmlMaps.Add( _
"http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_std.cgi?ico=03418197", _
"Ares_odpovedi").Name = "ARES"

'... kde je mapovani?

ActiveWorkbook.XmlMaps("ARES").DataBinding.Refresh

End Sub

Ano, je to tak, to podstatné – mapování – chybí, Záznamník maker jej nezaznamená. Co s tím? Pokud jste si všimli, data jsou umístěna v Tabulce (ListObject). Pokud tedy už máme načtena data z předchozího kroku a v Excelu je uloženo mapování, pak stačí jen aktualizovat zdroj Tabulky:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Sub ZmenitXMLData()

Dim lobTabulka As ListObject

Dim strNovyZdroj As String

'prirazeni Tabulky do objektove promenne
Set lobTabulka = ActiveSheet.ListObjects("TabulkaARES")

'zmena v odkazu na zdroj
strNovyZdroj = "http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_std.cgi?ico=25317075"

With lobTabulka.XmlMap.DataBinding

'vycisteni
.ClearSettings

'nahrani noveho zdroje
.LoadSettings strNovyZdroj

'aktualizace tabulky
.Refresh

End With

End Sub

No hurá, řetězec si můžeme slepovat a do programového kódu zapojit obsah buňky nebo třeba InputBox pro zadání IČO. Tuto drobnou úpravu už ponechám na vás.

XML a PowerQuery

Inu, je čas nedívat se dozadu, máme tu nové verze Excelu a PowerQuery. Tentokrát už v Excelu 2016 (v mém případě Preview 2019) vyjdeme z karty Data a volby Z webu (případně Data / Načíst data / Ze souboru / Ze souboru XML). V průvodci rovnou zapracujeme parametr pro IČO.

Excel - XML, PowerQuery, parametr — Excel – XML, PowerQuery, parametr

V dalším dialogu klepneme na tlačítko Upravit.

Dostáváme se do Editoru PowerQuery. To, co jsme výše označovali jako mapování XML, je zde v překladu nazýváno rozbalováním (tuším, že v angličtině je drill-down). Potřebujeme zkrátka rozkrýt schované informace v poměrně složité stromové struktuře a vyzobat potřebné sloupce. Upřímně přiznávám, že při prvním přiblížení PowerQuery a XML mi trvalo hodně dlouho, než jsem danou techniku objevil.

Excel - rozbalení dat v Editoru PowerQuery — Excel – rozbalení dat v Editoru PowerQuery

Výsledek rozbalování (v postupu viz také pojmy navigace, identifikace), skrývání sloupců, případně úprava typu a změna názvů v hlavičce může vypadat nějak takto:

Excel - výsledek v PowerQuery — Excel – výsledek v PowerQuery

PowerQuery opustíme s pomocí tlačítka Zavřít a načíst.

Excel - úprava parametru v PowerQuery — Excel – úprava parametru v PowerQuery

Jak ukazuje snímek, parametr lze upravit a následně aktualizovat data, nicméně změna probíhá po přepnutí zpět do Editoru PowerQuery a ergonomie jde do kopru.

Parametry v dotazech nejsou v Excelu nic nového. Pokud to šlo, hodnota se zadávala buď v dialogu při aktualizaci dat, nebo Excel nabízel možnost propojení na buňku. Ve správě parametru v PowerQuery máme k dispozici možnost vybírat IČO ze seznamu (přímo nebo jako výsledek dotazu), bohužel se tak děje v Editoru PowerQuery, ne v prostředí Excelu. A co tedy propojení s buňkou? Žádná přímá možnost v PowerQuery není! Pokud se podíváte do diskusí, bylo by zapotřebí vytvořit nový dotaz, jenž by vracel hodnotu patřičného typu. To je cesta do pekel tak jako tak, protože původní dotaz bude závislý na jiném a PowerQuery začne plivat hlášky typu „Formula.Firewall“.

Jestliže mi něco uniklo, pardon, rád doplním, ale v rámci dané problematiky jsem si prohloubil svou nenávist k „všechnopower“ a schizofrenii dotaženou do absurdity. Ve své podstatě neschopnost kontingenčních tabulek doplnil v novodobých verzích Excelech omezený slepenec PowerPivot a následně PowerQuery. Myšlenka snad byla dobrá, implementace hrozná. Data si přehazujeme z jedné ruky do druhé jak blázni (a pokud vám to nestačí, podívejte se po Power BI).

Import XML dat s využitím knihovny Microsoft XML

Ve VBA jsme ještě nevyčerpali s objektem XMLMaps všechny možnosti. Pro parsování zdroje informací z internetu jsem využil knihovny Microsoft XML a pro daný účel zpracoval čtyři vlastní funkce (UDF), použitelné jak v rámci kódu VBA, tak na listu Excelu. Byl jsem si přitom vědom toho, že častěji hledáme informace na základě názvu firmy, ne IČO. To bohužel není ideální. V názvech bývají chyby (typicky přístavek s. r. o) , i jedna firma může užívat více verzí (viz třeba doplněné slovíčko Czech) atd. Na druhou stranu, v praxi, pokud dáváte dohromady informace o společnosti ze dvou a více tabulek, pak právě klíčem by mělo být IČO!

pefFIRMAICO … vrací IČO podle názvu firmy
pefFIRMANAZEV … vrací název firmy podle IČO
pefFIRMADETAIL … maticová forma zadávání, vrací výčet informací o firmě podle IČO
pefFIRMAARES … maticová forma zadávání, vrací výčet informací o firmě podle názvu

Od uvedeného řešení a funkcí nečekejte příliš rychlou odezvu. Slouží především pro získávání jednorázových informací, například v rámci fakturace, a nehodí se pro hromadné zpracování. Kód VBA je otevřený a komentovaný.

Excel - načítání dat ze systému ARES — Excel – načítání dat ze systému ARES

Pokud si daného řešení ceníte, dejte vědět.

Co říci závěrem… Žádné řešení není všespásné, určitě nečekejte zázraky pro hromadný sběr informací. Informace o menší skupině firem ale takto dohledat můžeme. V tom případě se samozřejmě hodí možnost rychle měnit parametr (IČO/název firmy). Jako nejschůdnější se mi jeví cesta přes kartu Vývojář, prvotní načtení dat a upravená procedura NacistXMLData – viz výše. A cosi mi říká, že by bylo nejlepší si zpracovat ve Visual Studiu vlastní nástroj s prvky umístěnými v Podokně úloh.

Sešity ke stažení:
excel_ares.zip

Uložit článek jako PDF