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.
