Czy weryfikacja rachunku bankowego kontrahenta musi odbywać się tylko przez dedykowaną wyszukiwarkę Ministerstwa Finansów lub komercyjne rozwiązania, które w ostatnim czasie pojawiają się jak przysłowiowe grzyby po deszczu? Czy biała lista podatników VAT jest dostępna również z poziomu Excela i Power Query. Postanowiłem to sprawdzić, a ten wpis prezentuje jak krok po kroku można samodzielnie stworzyć takie narzędzie.
Uwaga! Interfejs API NBP uległ zmianie. Dlatego zamiast czytać poniższy poradnik, obejrzyj zaktualizowane nagranie. Z niego dowiesz się jak krok po kroku weryfikować podatników VAT z poziomu Excela.
Biała lista podatników VAT – co musisz wiedzieć
Biała lista (zamiennie używa się również określenia White List) to nowy wykaz podatników VAT, który zawiera informacje o czynnych podatnikach, wykreślonych z rejestru oraz przywróconych do niego.
Jest on dostępny od września 2019 r., a jego celem jest wsparcie procesu weryfikacji kontrahentów.
Biała lista pozwala sprawdzić bieżący status kontrahenta jako podatnika VAT, jak również potwierdzić numer rachunku bankowego, na jaki powinieneś mu zapłacić za zakupiony produkt lub usługę.
Biała lista – jak weryfikować kontrahentów?
Najprostszym sposobem jest wejście na stronę podatki.gov.pl, gdzie znajduje się dedykowana wyszukiwarka Wykazu podatników VAT. Korzystanie z niej nie wymaga wyjaśnień, obsługa jest intuicyjna.
Niestety możesz zweryfikować jednego kontrahenta w danym momencie. Tym samym, jeśli chciałbyś sprawdzić powiedzmy 50 firm, z którymi współpracujesz musiałbyś tą czynność powtórzyć aż pięćdziesiąt razy.
Biała lista – masowy dostęp dzięki API
Na szczęście Ministerstwo Finansów (MF) udostępniło tzw. interfejs programistyczny API (Application Programming Interface), który pozwala weryfikować jednorazowo większe „paczki” danych.
Oczywiście korzystanie z API nie jest idealne, ponieważ ustawiony jest limit zapytań jakie można wysłać na serwer MF. Jak to wygląda w rzeczywistości? Otóż w ciągu doby można wysłać 10 zapytań, każdy dotyczący maksymalnie 30 kontrahentów. Jeśli limit zostanie osiągnięty, to dostęp do bazy będzie zablokowany do północy.
Szczegółowe informacje dotyczące API są umieszczone na tej stronie.
Kontrahentów można weryfikować podając jedną z trzech informacji:
- Numer konta bankowego
- NIP
- REGON
W odpowiedzi, wiele firm konsultingowych oraz firm informatycznych proponuje w ostatnim czasie narzędzia automatyzujące ten proces. Jedne są płatne, drugie nie.
Biała lista – zrób to w Excelu
Postanowiłem przyjrzeć się temu z bliska i zobaczyć jakie możliwości w tym zakresie oferuje Excel oraz Power Query. W tym celu skorzystam z dostępnych numerów NIP i na ich podstawie przeprowadzona zostanie weryfikacja kontrahentów.
Po zapoznaniu się z dokumentacją dotyczącą API wiadomo, że zapytanie do bazy odnośnie numerów NIP powinno wyglądać tak: https://wl-api.mf.gov.pl/api/search/nips/XXXXXXXXXX,YYYYYYYYYY,ZZZZZZZZZZ?date=2019-11-02
W takim przypadku serwer zwróci dane dla numerów NIP oznaczonych tutaj literami X, Y i Z podając stan na dzień 2 listopada 2019 roku.
Poniżej opisuję krok po kroku całą procedurę przygotowania narzędzia, które automatycznie będzie weryfikowało status podatnika VAT i przypisywało numery kont bankowych.
Jeśli do tej pory nie korzystałeś z narzędzia Power Query lub znasz je bardzo pobieżnie, to pobierz Niezbędnik Księgowego.
KROK 1: Na początek stwórz listę numerów NIP w pliku, który należy zapisać w formacie CSV. Zapamiętaj jego lokalizację, ta informacja będzie potrzebna w kolejnym kroku. Plik w tym przykładzie będzie nazywał się: numery-nip-do-sprawdzenia.csv.
KROK 2: W Excelu utwórz zupełnie nowy skoroszyt, np. używając skrótu klawiaturowego Ctrl + N. Następnie pobierz dane z pliku źródłowego utworzonego w kroku 1. W tym celu przejdź na kartę Dane, gdzie w grupie Pobieranie i przekształcanie danych należy kliknąć polecenie Z pliku tekstowego/CSV.
W oknie dialogowym wskaż folder, w którym umieszczony jest plik numery-nip-do-sprawdzenia.csv, zaznacz plik i kliknij przycisk Importuj.
KROK 3: Na ekranie pojawi się okno dialogowe prezentujące podgląd zawartości pliku źródłowego. Kliknij przycisk Przekształć dane, aby przejść do edytora Power Query.
KROK 4: Po uruchomieniu edytora Power Query zobaczysz w jego centralnej części zawartość pliku źródłowego. Zanim przejdziemy do dalszej części procesu budowania narzędzia do weryfikacji podatników VAT zmień nazwę zapytania wprowadzając do właściwego pola opis NumeryNIP.
To będzie zapytanie, do którego na końcu zostaną dopisane numery kont bankowych oraz status podatnika VAT.
KROK 5: Na tym etapie pamiętaj, aby zmienić typ danych przechowywanych w kolumnie NIP. Wystarczy kliknąć na ikonę umieszczoną po lewej stronie nazwy kolumny, a następnie wybrać opcję Tekst.
KROK 6: W chwili obecnej posiadamy numery NIP wypisane jeden po drugim. Ponieważ chcemy wykorzystać interfejs API i zmaksymalizować zapytania dlatego powinniśmy je pogrupować po 30 numerów NIP w jednym wierszu. W tym celu będzie trzeba scalić zgromadzone tutaj numery NIP.
Ponieważ w przykładzie numerów NIP jest zdecydowanie mniej, dlatego będziemy je grupować po 5, żeby zademonstrować jaki będzie efekt końcowy. Oczywiście Ty tworząc to rozwiązanie u siebie możesz od razu użyć wartości 30.
Zanim przejdziesz do scalania numerów NIP należy utworzyć kopię bieżącego zapytania. W tym celu z lewej strony edytora kliknij na etykietę Zapytania, a następnie kliknij prawym przyciskiem myszy na zapytanie NumeryNIP i wybierz polecenie Duplikuj.
KROK 7: Zmień nazwę zduplikowanego zapytania na URLdlaAPI. Będzie ono zawierać gotowe adresy URL zawierające numery NIP do kontroli.
KROK 8: Na karcie Dodaj kolumnę w grupie Ogólne kliknij polecenie Kolumna indeksu. W naszym zapytaniu powstanie kolumna o nazwie Indeks z unikatowymi wartościami począwszy od 0.
KROK 9: Mając zaznaczoną kolumnę Indeks, ponownie wybierz kartę Dodaj kolumnę, gdzie w grupie Z liczby kliknij polecenie Standardowy, a następnie Podziel (liczby całkowite).
W oknie dialogowym Podziel bez reszty wprowadź wartość 5 (przypominam, że 5 używamy tylko w tym przykładzie, potem należy zamienić ją na 30) i kliknij przycisk OK.
Efektem tej operacji jest pojawienie się nowej kolumny Dzielenie z resztą, w której wartość wzrasta o 1 dokładnie co pięć wierszy. Już za chwilę numery NIP z przypisaną wartością 0 w tej kolumnie będą umieszczone w jednym wierszu, a numery z wartością 1 w kolejnym, itd.
KROK 10: Ponownie zaznacz kolumnę Indeks i przejdź na kartę Przekształć. W grupie Kolumna liczb wybierz polecenie Standardowy, a następnie kliknij Modulo.
W oknie dialogowym Modulo wprowadź 5 (potem wpiszesz tutaj 30) i kliknij OK.
Jak widzisz, teraz wartości w kolumnie Indeks są numerowane od 0 do 4 i od nowa.
KROK 11: Przyszedł czas na przekształcenie bieżącego zapytania. Upewnij się, że zaznaczyłeś kolumnę Indeks. Teraz na karcie Przekształć w grupie Dowolna kolumna kliknij polecenie Kolumna przestawna.
W oknie dialogowym Kolumna przestawna upewnij się, że jako kolumna wartości wybrana jest kolumna o nazwie NIP. Rozwiń Opcje zaawansowane, gdzie z listy rozwijanej należy wybrać opcję Nie agreguj. Kliknij przycisk OK.
Brawo. Właśnie przekształciłeś dane w taki sposób, że w każdym wierszu mamy po 5 numerów NIP.
KROK 12: Kliknij prawym przyciskiem myszy na nagłówku kolumny Dzielenie z resztą, a następnie wybierz polecenie Usuń.
KROK 13: Aby scalić wszystkie kolumny zawierające numery NIP w jedną kliknij w nagłówek kolumny 0. Wciśnij i przytrzymaj klawisz Shift i kliknij nagłówek kolumny 4.
Na karcie Przekształć w grupie Kolumna tekstu wybierz polecenie Scal kolumny. Ponieważ klucz API wymaga danych oddzielonych od siebie przecinkami, dlatego w oknie dialogowym Scal kolumny określ przecinek jako separator oraz nadaj kolumnie nazwę ZestawyNIP. Kliknij przycisk OK.
KROK 14: Teraz przyszedł czas na utworzenie kolumny niestandardowej zawierającej odwołania do strony Ministerstwa Finansów, czyli inaczej rzecz ujmując adres strony internetowej. W tym celu na karcie Dodaj kolumnę w grupie Ogólne kliknij polecenie Kolumna niestandardowa.
W oknie dialogowym Kolumna niestandardowa podaj nazwę dla nowotworzonej kolumny AdresURL, a w polu Formuła kolumny niestandardowej wprowadź następującą formułę:
=”https://wl-api.mf.gov.pl/api/search/nips/” & [ZestawyNIP] & “?date=” & DateTime.ToText(DateTime.LocalNow(),”yyyy-MM-dd”)
Ta formuła tworzy adres internetowy zawierający zestawy numerów NIP oraz bieżącą datę w formacie ROK-MIESIĄC-DZIEŃ. Dzięki temu zawsze będziemy otrzymywać bieżący status podatników VAT.
KROK 15: Kliknij na pierwszy adres w nowej kolumnie. Na dole okna edytora Power Query pojawi się pełna ścieżka dostępu. Zaznacz ją myszą i skopiuj do schowka używając skrótu klawiaturowego Ctrl + C.
KROK 16: Przejdź na kartę Strona główna. W grupie Nowe zapytanie kliknij Nowe źródło, potem Inne źródła, a następnie Internet.
W oknie dialogowym Z sieci Web w polu Adres URL wklej wcześniej skopiowany adres używając skrótu Ctrl + V i kliknij przycisk OK.
KROK 17: Dla nowego zapytania zmień nazwę na ZapytanieAPI.
KROK 18: Teraz kliknij w komórkę zawierającą wpis Record. Następnie kliknij w tym samym miejscu na wpis List. W ten sposób uzyskaliśmy listę rekordów.
KROK 19: Odszukaj po lewej stronie edytora Power Query zapytanie ZapytanieAPI i kliknij prawym przyciskiem myszy. Wybierz polecenie Edytor zaawansowany.
Aby budowane rozwiązanie mogło za każdym razem weryfikować numery NIP łącząc się ze stroną Ministerstwa Finansów należy zmodyfikować treść zapytania.
W tym celu na samej górze należy umieścić definicję zmiennej mojAdresURL. Również w źródle adres w cudzysłowie należy zastąpić nazwą tej zmiennej. Na końcu kliknij przycisk Gotowe.
Zauważ, że zapytanie zostało przekształcone w funkcję. Bardzo dobrze, o to chodziło!
KROK 20: Wróć do zapytania URLdlaAPI. Przejdź na kartę Dodaj kolumnę, a następnie w grupie Ogólne wybierz polecenie Wywołaj funkcję niestandardową.
W oknie dialogowym Wywołaj funkcję niestandardową nadaj nazwę dla nowej kolumny (DaneAPI), wybierz zapytanie funkcji (ZapytanieAPI) oraz wskaż, w której kolumnie znajduje się AdresURL. Kliknij przycisk OK.
Jeśli na ekranie pojawi się komunikat “Wymagane są informacje dotyczące poprawności danych”, kliknij przycisk Kontynuuj.
W oknie dialogowym Poziomy prywatności zaznacz opcję Ignoruj… i kliknij Zapisz.
KROK 21: W zapytaniu pojawiła się nowa kolumna o nazwie DaneAPI. Kliknij na jej nagłówek prawym przyciskiem myszy i wybierz polecenie Usuń inne kolumny.
KROK 22: Rozwiń kolumnę DaneAPI klikając w ikonę umieszczoną z prawej strony jej nazwy. Wybierz opcję Rozwiń do nowych wierszy.
KROK 23: Pojawiła się lista rekordów, które również trzeba rozwinąć. W oknie dialogowym kliknij polecenie Załaduj więcej, aby wczytać wszystkie pola z listy oraz odznacz opcję Użyj oryginalnej nazwy kolumny jako prefiksu. Kliknij przycisk OK.
W ten sposób uzyskałeś listę danych pobranych ze strony Ministerstwa Finansów, ale nie jest ona jeszcze w pełni funkcjonalna.
Przesuń tabelę do miejsca, gdzie będziesz widział kolumny representatives, authorizedClerks, partners. Dla każdej z nich naciśnij ikonę rozszerzenia.
Teraz przesuń tabelę, aby przejść do kolumny accountNumbers i tutaj również rozwiń listę.
Super! Uzyskałeś pełną listę danych.
KROK 24: Przejdź na kartę Strona główna i w grupie Zamknij kliknij napis Zamknij i załaduj, a następnie wybierz opcję Zamknij i załaduj do.
Zostaniesz przeniesiony do Excela, gdzie wyświetli się okno dialogowe Importowanie danych, w którym zaznacz opcję Utwórz tylko połączenie i kliknij przycisk OK.
Za chwilę po prawej strony okna aplikacji pojawi się okno dialogowe Zapytania i połączenia, gdzie będą widoczne wszystkie elementy, które utworzyłeś. Wszystkie są w postaci tylko połączeń. Ale spokojnie, zaraz przejdziemy do utworzenia ostatecznego raportu w postaci tabeli.
KROK 25: Na karcie Dane w grupie Pobieranie i przekształcanie danych kliknij Pobierz dane, a następnie Połącz zapytania i Scal.
W oknie dialogowym Scalanie wybierz jako pierwsze zapytanie NumeryNIP i kliknij na nagłówek kolumny NIP, a następnie jako drugie zapytanie wybierz URLdlaAPI i również wskaż kolumnę nip. Kliknij przycisk OK.
KROK 26: Edytor Power Query wyświetli nowe zapytanie, gdzie ostatnią kolumnę URLdlaAPI należy rozwinąć i wybrać interesujące pola, które mają być widoczne.
Pamiętaj, aby odznaczyć opcję Użyj oryginalnej nazwy kolumny jako prefiksu.
KROK 27: Zmień nazwę zapytania na ZweryfikowaniPodatnicyVAT.
KROK 28: Na karcie Strona główna w grupie Zamknij kliknij polecenie Zamknij i załaduj. Excel utworzy nowy arkusz, w którym umieści tabelę zawierającą wszystkie dane odpowiadające numerom NIP umieszczonym w pliku źródłowym, który importowałeś na samym początku.
KROK 29: Ten krok jest najważniejszy. Zapisz gotowy raport, w przeciwnym razie cały proces będziesz musiał wykonać od nowa.
Co dalej?
Kiedy będziesz chciał zweryfikować kolejny zestaw numerów NIP wystarczy, że w pliku źródłowym (format CSV w prezentowanym przykładzie) wprowadzisz nowe wartości i zapiszesz zmiany. Następnie otwierasz plik z utworzonym raportem, przechodzisz do arkusza z tabelą, a następnie klikasz na niej prawym przyciskiem myszy i wybierasz polecenie Odśwież. GOTOWE!
Acha, pamiętaj o dostępie do internetu. W przeciwnym razie Power Query nie będzie mógł połączyć się z serwerem Ministerstwa Finansów.
Film instruktażowy
Jako uzupełnienie powyższego wpisu i chęć udzielenia odpowiedzi na większość komentarzy i zapytań jakie spłynęły do mnie w ostatnim czasie, przygotowałem i opublikowałem nagranie video. Zapoznanie się z nim zdecydowanie ułatwi proces budowania narzędzia do weryfikacji numerów rachunków bankowych kontrahentów.
Podsumowanie
Jak widzisz biała lista podatników VAT jest również dostępna z poziomu Excela i Power Query. Mam nadzieję, że zaprezentowany przykład przekonał Cię do tego narzędzia. Pozwala ono optymalizować pracę, automatyzować ją i oszczędzać sporo czasu.
A jakie jest Twoje doświadczenie w pracy z tym narzędziem? Umieść odpowiedź w komentarzu poniżej wpisu.