Bank statements analysis in Mathematica
Bank statements analysis in Mathematica
In this file I am going to investigate my own outgoing expenses from my online banking. First I will import the first six months of 2017 as a comma-separated value (CSV) file. Second I will use some Mathematica built-in functions in order to classify my personal outgoing expenses. Such a classification will allows me to obtain the trend of each class.
Note that this analysis is basically the same one for every class of my personal outgoing expenses. Thus, I will comment only the first class, i.e. Supermarket expenses.
Note that this analysis is basically the same one for every class of my personal outgoing expenses. Thus, I will comment only the first class, i.e. Supermarket expenses.
Importing CSV file and Visualization of my Dataset
Importing CSV file and Visualization of my Dataset
Importing CSV file:
fileImp=Import["f5.csv"];
Dimension of data frame:
Dimensions[fileImp]
{193,4}
Data frame visualization:
Dataset[fileImp]
Dimensions[Dataset[fileImp]]
{193,4}
Transforming our data frame into a list of associations and padding the associations with zeros where we have no values:
named=Map[AssociationThread[First[fileImp]PadRight[#,4]]&,Rest[fileImp]];
Selecting specific columns and changing date format
Selecting specific columns and changing date format
Selecting interested columns:
selectedColumns=named[[All,{"Buchungstag","Beguenstigter/Zahlungspflichtiger","Betrag"}]];
Dataset[selectedColumns]
Changing date format from “Day.Month.Year” into “Day,Month,Year” in the date column:
Bstag=named[[All,{"Buchungstag"}]];
InterpDataE=Interpreter["StructuredDate",DateFormat{"Day",".","Month",".","YearShort"}];
InterpDataE[Bstag];
Selecting the month and year from each element of date column and creating a new column in the data frame for each of them:
prova=MapAt[InterpDataE,selectedColumns,{1;;,1}];
InterpDataE2=Map[With[{d=#Buchungstag},<|"month"->d[[1,2]],"year"->d[[1,1]],#|>]&,prova];
Dataset[InterpDataE2]
List of Month
List of Month
In order to plot the following results I need to have a list of the first of six months
Months={1,2,3,4,5,6};
Supermarket expenses
Supermarket expenses
Let us focus on my supermarket expenses. This analysis takes into consideration the following four supermarkets: Rewe, Tegut, Karstadt, Netto. Such supermarkets are extracted by using the following command:
Supermarkets=Select[InterpDataE2,#[[4]]"ALDI GmbH + Co. KG HANN.MUENDEN"||#[[4]]"NETTO MARKEN-DISCOU."||#[[4]]"tegut... gute Lebens"||#[[4]]"REWE-Markt Moewes oHG"||#[[4]]"KARSTADT WARENHAUS GMHB"&];
Dataset[Supermarkets]
Number of lines in this new association:
M=Supermarkets[[All,"month"]];
Length[M]
31
Extracting relevant data for each month:
MM6=Select[Supermarkets,#[[1]]6&];MM5=Select[Supermarkets,#[[1]]5&];MM4=Select[Supermarkets,#[[1]]4&];MM3=Select[Supermarkets,#[[1]]3&];MM2=Select[Supermarkets,#[[1]]2&];MM1=Select[Supermarkets,#[[1]]1&];
Computing the total amount of the column “Betrag” for each month:
SMM6=Total[MM6[[All,"Betrag"]]];SMM5=Total[MM5[[All,"Betrag"]]];SMM4=Total[MM4[[All,"Betrag"]]];SMM3=Total[MM3[[All,"Betrag"]]];SMM2=Total[MM2[[All,"Betrag"]]];SMM1=Total[MM1[[All,"Betrag"]]];
Making a list of the sums above:
SMMN={SMM1,SMM2,SMM3,SMM4,SMM5,SMM6};
In order to plot “month vs total amount” for my supermarket expenses, I will use the built-in function “timeseries” on the two lists: SMMN, Months.
tsSupermarketsM=TimeSeries[SMMN,{Months}];
Here we plot our data by using listplot:
Rent expenses
Rent expenses
Let us focus on my rent expenses. This analysis takes into consideration all rents in the studied period of time.
Visa card expenses
Visa card expenses
Let us focus on my visa card expenses. This analysis takes into consideration all uses of my visa card online or in direct payments in the studied period of time.
All together
All together
Further Explorations
Further Explorations
A further exploration would be cleaning this code and using many more built-in functions in order to make it more efficient and beautiful.
Authorship information
Authorship information
Federico Dradi,
Friday, June 23rd 2017
federico.dradi@gmail.com
Friday, June 23rd 2017
federico.dradi@gmail.com