
Wystawiasz faktury w Excelu? Jedną z wad tego rozwiązania jest konieczność ręcznego wpisywania wszystkich danych nabywcy faktury. Programy księgowe mają opcję automatycznego uzupełniania tych danych po podaniu numeru NIP. Postanowiłem opracować prosty moduł, który wzbogaca Excela o możliwość pobierania danych nabywcy na podstawie samego numeru NIP. Moje rozwiązanie nie wymaga korzystania z makr.
Pobierz przykładowe pliki (Excel, LibreOffice, Word)
Zasada działania jest podobna do opracowanej przez mnie funkcji słownie. W skrócie należy wywołać odpowiedni adres URL z parametrem jakim jest numer NIP, aby w odpowiedzi otrzymać dane nabywcy. W Excelu od wersji 2013 istnieje funkcja WEBSERVICE, która pobiera zawartość strony internetowej. Aby pobrać dane Orlenu należy w komórce B1 wpisać NIP Orlenu czyli 7740001454 a w komórce B2 wpisz taką oto formułę:
=WEBSERVICE("https://api.officeblog.pl/gus.php?NIP="&B1&"&format=0")
W odpowiedzi zostanie zwrócony komplet danych w formacie XML:
<root> <dane> <Regon>610188201</Regon> <Nip>7740001454</Nip> <StatusNip /> <Nazwa>POLSKI KONCERN NAFTOWY ORLEN SPÓŁKA AKCYJNA</Nazwa> <Wojewodztwo>MAZOWIECKIE</Wojewodztwo> <Powiat>Płock</Powiat> <Gmina>Płock</Gmina> <Miejscowosc>Płock</Miejscowosc> <KodPocztowy>09-411</KodPocztowy> <Ulica>ul. Chemików</Ulica> <NrNieruchomosci>7</NrNieruchomosci> <NrLokalu /> <Typ>P</Typ> <SilosID>6</SilosID> <DataZakonczeniaDzialalnosci /> <MiejscowoscPoczty>Płock</MiejscowoscPoczty> </dane></root>
Chcąc wyłuskać dane z formatu XML możemy skorzystać z funkcji: FILTERXML np.
=FILTERXML(B2;"//Regon")
Co oznacza, że zostaną wydobyte dane znajdujące się pomiędzy TAGami <Regon> i </Regon>. Jeśli dany TAG nie istnieje (np. podałeś nieprawidłowy NIP w związku z czym zwrócone dane w formacie XML nie zawierają TAGu <Regon> funkcja FILTERXML zwróci błąd #ARG!. W tym celu „dla estetyki” warto opakować ową funkcję w funkcję JEŻELI.BŁĄD co da nam taką postać formuły:
=JEŻELI.BŁĄD(FILTERXML(B2;"//Regon");"")
Drugi argument funkcji JEŻELI.BŁĄD "" mówi nam, że w zamiast błędu #ARG! ma zostać zwrócony pusty ciąg znaków. Równie dobrze zamiast "" można by było wpisać "brak danych" lub jakikolwiek inny tekst.
Niestety, ale jeśli chcemy pobrać więcej danych, to trzeba się trochę pobawić tj. każdą daną w osobnej komórce wyławiamy za pomocą funkcji FILTERXML. Jeśli szablon projektowanej przez Ciebie faktury dopuszcza, aby komplet danych pojawił się w jednej komórce możesz w funkcji WEBSERVICE zmienić wartość parametru format na 1 (dane adresowe będą rozdzielone enterami) lub 2 (dane adresowe będą w jednym wierszu rozdzielone przecinkami).
=WEBSERVICE("https://api.officeblog.pl/gus.php?NIP="&B1&"&format=1")
Zwróci nam
POLSKI KONCERN NAFTOWY ORLEN SPÓŁKA AKCYJNA
ul. Chemików 7
09-411 Płock
Z kolei:
=WEBSERVICE("https://api.officeblog.pl/gus.php?NIP="&B1&"&format=2")
Zwróci nam:
POLSKI KONCERN NAFTOWY ORLEN SPÓŁKA AKCYJNA, ul. Chemików 7, 09-411 Płock
Libre Office
W LibreOffice zasada działania jest podobna z tym, że odpowiednik funkcji WEBSERVICE to USŁ.INTER a odpowiednik funkcji FILTERXML to FILTR.XML.
W przypadku zwracania danych z parametrem format=1 (dane w 3 linijkach rozdzielonych enterami) Libre Office przed znakiem Entera wstawił "jakieś śmieci", ale dodanie w adresie URL parametru BOM=1 usunęło problem.
Dziwne kodowanie znaków
Jeśli skorzystasz z mojej usługi w jakimś innym programie pakietu biurowego i zamiast polskich znaków zobaczysz krzaczki, dodaj w adresie URL dodatkowy parametr BOM=1 tj. zamiast wywoływać URL:
https://api.officeblog.pl/gus.php?NIP=7740001454&format=0
wywołaj adres:
https://api.officeblog.pl/gus.php?NIP=7740001454&format=0&BOM=1
Parametr BOM=1 powoduje, że moje dane dodatkowo na początku zawierają znacznik kolejności bajtów BOM informujący, że zwracane dane są kodowane w formacie UTF-8.
Uwagi końcowe
Usługa jest darmowa, napisałem ją hobbystycznie. Prosiłbym o zachowanie umiaru tj. jeśli prowadzisz małą firmę i wystawiasz w Excelu miesięcznie kilkanaście faktur to OK możesz skorzystać z mojej usługi, aby ułatwić sobie pracę.
Nie wyrażam zgody na używanie jej w celu np. masowego pobierania danych setek firm w krótkim okresie czasu (tj. skanowanie kolejnych numerów NIP np. 0000000001, 000000002, 0000000003 itp.)
Literatura uzupełniająca
Jeśli jesteś programistą i chcesz wdrożyć w swoim programie tę funkcjonalność to w pierwszej kolejności zapoznaj się ze stroną portalu API GUS. Znajduje się tam dokumentacja techniczna oraz informacja, jak pozyskać własny klucz API (kwestia napisania jednej wiadomości e-mail), o dziwo maila napisałem późnym wieczorem a odpowiedź wraz z własnym kluczem API otrzymałem następnego dnia roboczego o godzinie 8:25. Usługa jest darmowa.
Jeśli programujesz w PHP możesz pobrać gotową bibliotekę autorstwa Krzysztofa Kuli. Jeśli programujesz w VBA zapoznaj się z tym projektem. Ogólnie w dokumentacji GUSu (plik BIR11_Przyklady.pdf) na końcu znajdują się przykłady dla innych języków programowania (m.in. JS, Python, Go, VB.NET, C#)
Aktualizacja 02.12.2022
Utworzyłem nową wersję wtyczki, która potrafi zwrócić dodatkowe informacje takie jak np. data zawieszenia działalności gospodarczej. Informacje znajdziesz w notce: Excel – pobieranie z GUS danych firmy na podstawie numeru NIP – raport szczegółowy
Obecna (stara) wersja wtyczki będzie równolegle funkcjonowała z nową wersją.
Dziękuję za Twoją pomoc, stworzyłem sobie dzięki temu wymarzony arkusz do generowania ładnych firmowych faktur.
Mam uwagę, że w formatach 1 i 2 API zwraca niepoprawne adresy gdy firma zlokalizowana jest nie pod adresem gdzie jest ulica, tylko np. miejscowość i numer domu.
Myślę, że łatwo można to naprawić dodając do tego formatu miejscowość jeśli ulica nie posiada wartości. Nie jestem programistą więc możliwe, że plotę bzdury o tym jak to naprawić 😉
Dziękuję za komentarz, usterka została poprawiona
Fantastyczna robota 🙂
Czegoś takiego szukałem. Brakuje mi do pełni szczęścia jednej kluczowej danej – mianowicie daty zawieszenia działalności.
Potrzebuje sobie raz w miesiącu skontrolować parę NIPów. Fajna to by była funkcja 🙂
Jest taka opcja. należy używać parametru format=0 gdyż format=1 lub format=2 służy do tego aby ładnie adres w formacie tekstowym sformatować.
Jeśli firma istnieje to w zwróconym ciągu znaków znajdziemy znacznik XML: <DataZakonczeniaDzialalnosci />
Jeśli firma jest wykreślona to mamy: <DataZakonczeniaDzialalnosci>RRRR-MM-DD</DataZakonczeniaDzialalnosci> (zamiast RRRR-MM-DD pojawi się data wykreślenia firmy)
Zakończenia – ok
Mi się firmy na chwilę "zawieszają" i datę zawieszenia działalności bym chciał wyświetlać.
Dziękuję za pomoc w zrozumieniu tematu. Niebawem będzie nowa wersja która będzie uwzględniała takie tematy jak np. chwilowe zawieszenie działalności.
Opracowałem nową wersję wtyczki, która pobiera dane z raportu szczegółowego:
https://www.officeblog.pl/excel-pobieranie-z-gus-danych-firmy-na-podstawie-numeru-nip-raport-szczegolowy/
Fenomenalne narzędzie! Bardzo mi się przydało