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

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.plPower 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.

Rysunek 1 JPK_KR otworzony w edytorze tekstu
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).

Rysunek 2 JPK_KR otwierany w Excelu
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).

Rysunek 3 JPK_KR otwierany w Excelu
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_.

Rysunek 4 Przekształcanie danych w Power Query
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_.

Rysunek 5 Przekształcanie danych w Power Query
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.