Magdalena Chomuszko

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

Excel tylko dla księgowych

Redaktor Magdalena Chomuszko

Projektant okładki Magdalena Chomuszko

© Magdalena Chomuszko, 2023

© Magdalena Chomuszko, projekt okładki, 2023

Kolejna publikacja z serii Excel tylko dla księgowych. Podobnie jak w innych, wcześniejszych publikacjach z tej serii i do tej dołączony jest plik Excela z omawianym w książce raportem, którym jest rachunek przepływów pieniężnych metodą pośrednią. Raport ten został zaprojektowany w Excelu, jako zestaw raportów:

1. Raport przepływów pieniężnych,

2. Bilans,

3. Rachunek zysków i strat,

4. Zestawienie zmian w kapitale.

Raport zasilany jest danymi z JPK_KR, przekształconymi za pomocą Power Query.

ISBN 978-83-8324-608-6

Książka powstała w inteligentnym systemie wydawniczym Ridero

Spis treści

1. Rachunek Przepływów Pieniężnych zasilany JPK_KR i Power Query

2. Wstęp

3. Power Query — obróbka danych

4. Projekt raportu

5. Zasilanie raportu danymi

6. Plik do pobrania

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.

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.

Koniec darmowego fragmentu książki.

Zachęcamy do zakupu pełnej wersji.