Bezpłatny fragment - Rachunek Przepływów Pieniężnych zasilany JPK_KR i Power Query

Wstęp

Na temat rachunku przepływów pieniężnych wyliczanego przy wsparciu zaprojektowanych raportów w Excelu i zasilanych danymi z pliku JPK_KR, została już wydana jedna publikacja. Załączone do niej pliki Excela z raportami RPP zostały poddane gruntownym testom poprzez wiele osób i firm, które zaczęły z raportów korzystać. Okazało się, że zasilenie raportów Excela danymi z JPK_KR działa całkiem nieźle, jednak tylko dla plików JPK_KR, których rozmiar nie jest większy niż pięćset MB. Przy większej ilości danych importowanie danych do raportu było już trudniejsze i nie zawsze się udawało. W związku z tym powstało wyzwanie, aby przygotować raport przepływów pieniężnych (wraz z bilansem i rachunkiem wyników), do którego będzie można wczytać nawet bardzo duże pliki JPK_KR.

Analizując zawartość plików JPK_KR, można w nim wyodrębnić następujące obszary:

— Plan kont,

— Zestawienie obrotów i sald,

— Dziennik,

— Zapisy.

Do raportu przepływów pieniężnych wykonanego metodą pośrednią, do bilansu i rachunku wyników potrzebne są tylko dane z zestawienia obrotów i sald, wobec czego reszta danych z pliku JPK_KR jest niepotrzebna. Dzięki funkcjonalności Power Query dane z pliku JPK_KR można opracować tak, aby móc skorzystać z nich tylko w takim zakresie, jaki jest potrzebny. Poza tym Power Query trzyma dane poza skoroszytem Excela, a to oznacza, że nie obciąża samego raportu, czyli plik Excela nie zwiększa znacząco swojego rozmiaru. Raport zaprojektowany w Excelu i zasilany danymi z zapytania Power Query w przypadku, gdy ma być przeniesiony w inne miejsce wymaga także aktualizacji informacji o lokalizacji danych do raportu (pliku JPK_KR). Wszystkie te niuanse opisane są w niniejszej publikacji.

Jeśli ktoś zakupi tę pozycję bez wcześniejszego obejrzenia filmu na moim kanale na YouTube @MChomuszko, zachęcam do zapoznania się z tym filmem. Zapraszam także na moją stronę: www.wirtuozksiegowosci.pl

Power Query — obróbka danych

Wszystkie pliki JPK są plikami XML. Format XML jest formatem tekstowym, który można dość łatwo przeczytać. Jednak szybka interpretacja danych zawartych w pliku XML już jest niemożliwa poprzez samo czytanie go. (rys. 1). By móc analizować dane zawarte w plikach JPK, można do tego wykorzystać Excel. To, jak można otworzyć plik JPK za pomocą Excela zostało opisane we wcześniejszych moich publikacjach, m.in. w książce JPK_KR z Excelem lub Power Query i JPK

Dane z plików JPK mogą być źródłami danych dla różnych raportów finansowo — księgowych. Jak wspomniano wcześniej w sytuacji gdy plik JPK zawiera bardzo dużo danych, to powoduje, że jest on bardzo duży i tym samym raport pracuje ciężko i wolno, lub w ogóle nie chce się uruchomić, wówczas właśnie warto skorzystać z możliwości raportowych jakie daje Power Query.

Zanim zaczniemy projektować raport, trzeba poddać obróbce dane z pliku JPK za pomocą Power Query tak, aby były one użyteczne dla projektowanych raportów. Naszym celem będzie wyciągnięcie z pliku JPK_KR samego zestawienia obrotów i sald (ZOiS). W pierwszym kroku, pobieramy dane z JPK_KR do arkusza kalkulacyjnego. W tym celu otwieramy pusty skoroszyt i przechodzimy na kartę Dane (rys. 2).

Z górnego menu wybieramy polecenie Pobierz dane i w podmenu wskazujemy polecenie: Z pliku XML

W kolejnym kroku zostanie otworzone okno, w którym trzeba wskazać plik JPK do wczytania do Excela (rys. 3).

Po zaimportowaniu wskazanego pliku, zobaczymy zawartość wczytanego pliku JPK_KR (rys. 4). Dane te są prezentowane w sposób czytelny i uporządkowany. Jak to zostało wcześniej napisane, do raportu przepływów pieniężnych, bilansu i RZiS, potrzebne są tylko dane z zestawienia obrotów i sald (ZOiS) oraz dane z nagłówka (okres, którego dotyczą dane), więc tylko te pozycje należy zaznaczyć po lewej stronie Nawigatora. Wcześniej klikamy w polu u góry Wybierz wiele elementów.

Po zaznaczeniu potrzebnych danych, klikamy na przycisku Przekształć dane, który znajduje się w prawej dolnej części okna (rys. 4). Po wykonaniu w/w opisanych czynności nastąpi przekształcenie pobranie z zapytania zaznaczonych danych i ujrzymy wyedytowane zapytanie (rys. 5).

Pobrane dane z pliku JPK_KR trzeba trochę poprawić, aby nadawały się jako źródło danych do projektowanego raportu. Poprawianie danych jest właśnie możliwe w edycji zapytania (rys. 5).

W pierwszym kroku porządkowania danych usuwamy kolumny:

— TypKonta,

— KodZespołu,

— OpisZespolu,

— KodKategorii,

— OpisKategorii,

— KodPodkategorii,

— Attribute: typ.

Usuwanie kolumn wykonujemy za pomocą przycisku w górnej części okna Usuń kolumny.

Następnie zaznaczamy kolumny z wartościami (kwotami) i przechodzimy na zakładkę Przekształć i wybieramy polecenie Zamienianie wartości (rys. 7). Wszystkie dane mają format tekstowy. Aby można było zasilić nimi raport finansowy, trzeba dane z kolumn:

— Bilans otwarcia (Wn, Ma),

— Obroty (Wn, Ma),

— Obroty Narastająco (Wn, Ma),

— Salda (Wn, Ma),

zamienić na format walutowy.

W tym celu, w oknie, które zostanie otworzone (rys. 6), w górnym polu wpisujemy kropkę, a w dolnym polu przecinek. Znak, który rozdziela liczby całkowite od ułamkowych musi być przecinkiem, aby możliwa była zamiana tekstu w/w kolumnach na walutę. Po zastąpieniu kropki przecinkiem, można już zamienić tekst na walutę. Zamiany tej dokonujemy poprzez kliknięcie w górnym lewym rogu każdej kolumny, gdzie można zamienić oznaczenie ABC na $ Waluta (rys. 7).