Use a String For a Variable name and convert Dictionaries to DataFrames

[brett_knoss]

I’m working on an accounting program, and I want to be able to have a function or macro that adds new account values to my dataframe, or another form that works as a CSV, XLSX or Database.

Accounts = Dict(
	# A Dictionary of all my accounts
	## List of Accounts and Starting Balance
	
	# Chequing
	
	"Chequing" => [0], #replace with balance as of Jan 1, 2019
	
	#Assets
	"Accounts Reciveable" => [0], #replace with balance as of Jan 1, 2019
	
	#Debts
	"Accounts Payable" =>[0])
spreadsheet = DataFrame(
Date=["Jan 1, 2000"], #start of the new year

chequing=[0], 

accounts_reciveable=Accounts["Accounts Reciveable"],
	
#Debts
#Debts--Credit when owed payment, Debit when recive payment

accounts_payable= Accounts["Accounts Payable"])

macro transaction(
		Journal_Entry,
		newdate,
		newdebit,
		debit_amount,
		newcredit, 
		credit_amount,
		new_memo)
	
	ex=quote
 	   if $credit_amount != $debit_amount
			"Error--Debit and Credit must match"
       else
			
				push!(spreadsheet,(defaultRow...,
					Accounts["Chequing"]=
						0,
					
						Accounts["Wages"] = $debit_amount,
						Accounts["Personal"]= $credit_amount,
						Date=$newdate,
						memo=$new_memo
						)) 


			#figure out how to delete this row, and get around repeat variables
			# I might have to save each transaction to the spreadsheet, at this stage
		end
		
		if $credit_amount == $debit_amount
			
			$Journal_Entry=DataFrame(
				EntryDate=[$newdate,"","",""],
				Credited_Accounts=["", $newcredit,"",""],
				Debited_Accounts=["","",$newdebit,""],
				Credit=[0,$credit_amount,0,0],
				Debit=[0,0,$debit_amount,0],
				Balance=[0,0,0,0])

		end
	end
	esc(ex)
	
end

@transaction(JE3,"Jan 1, 2019", "Wages", 1000, "Chequing", 1000,"this is a test")

How do I get this to work?

[xiaodai]

do you have a SAS background or something? wondering why u r using a macro. I didn’t read the code as it’s too long.

If you can simplify your problem and make an Minimal Working Example by following this guide

Please read: make it easier to help you

Welcome to the Julia Discourse! We are enthusiastic about helping Julia programmers, both beginner and experienced. This public service announcement (PSA) outlines best practices when asking for help. Following these points makes it easier for us to help you and more likely you’ll get a prompt, useful answer. Keywords are highlighted to make it easier to refer to specific points. Choose a descriptive title that captures the key part of your question, eg “plots with multiple axes” instead of …

An example is this really easy to follow post on how to ask question to maximize the chances of getting help:

Sub-sample array until a target number of unique elements is left

Hey! I’m in need of a fast method that uniformly subsamples the elements of a given array until only a specified number of unique elements is left. For example input array=[4,1,2,2,4] and target_unique_el=2, then the (random) result could be res=[4,2,2] (as it has two unique elements left). My current implementation is: using Random using BenchmarkTools function subsample_unique!(array, target_unique_el) shuffle!(array) # for uniform sampling unique_el = length(Set(array)) while …

For me, the original is too long and the code is too long. So I just switched and don’t wanna do it. I’d say, the more prep work you do the more likely you will get help in a timely manner.

[nilshg]

I don’t think the example is necessarily too long, just a bit convoluted. My question would be the same though, why do you think you need a macro? It seems like all you need is a DataFrame that you push! new rows to for each transaction, and then (maybe?) calculate a new balance after the transaction in an additional column?

[brett_knoss]

The macro is because I plan to use it over and over. I could just use the dataframe, but I wanted to have the name of the account in a dataframe and I wasn’t able to do that with a dataframe.

[nilshg]

I’m still 99.9% sure that you don’t need (and shouldn’t use) a macro here - using something “over and over” is not a reason to use a macro. In most scenarios that’s the use case a function is for.

In any case it’s quite hard to understand what you’re actually trying to do. Could you mock up an example of your desired use case and output? Doesn’t have to be working code, but at least it should help others understand the structure of your envisaged database.

[oheil]

Despite that the others are right, that this should be a function, not a macro, but looking at the code, it seems, that there is something wrong in your push! line of the macro:

				push!(spreadsheet,(defaultRow...,
					Accounts["Chequing"]=0,
						Accounts["Wages"] = $debit_amount,
						Accounts["Personal"]= $credit_amount,
						Date=$newdate,
						memo=$new_memo
						)) 

First I can’t find why there is defaultRow..., in the Tuple. There is no hint to it in the documentation “Functions · DataFrames.jl” , so my guess is, this must be removed. Perhaps it’s from an older version of DataFrames, I am on v1.1.1.

But still, the Tuple doesn’t match your spreadsheet. It has 4 columns, and the Tuple you provide in push! has 5 columns. It seems you are mixing up your spreadsheet with that Journal_Entry you create.

[xiaodai]

@brett_knoss

macro is because I plan to use it over and over

Maybe in SAS u would use a macro for. I think what u want is a function. But the example is too long. Distill it to the simplest u can think of.

[brett_knoss]

No, I think the problem is that trying to have dictionaries and dataframes

This did work

Accounts=DataFrame(
Date=["Jan 1, 2000"]
Chequing=[0],
Income=[0],
Expenses=[0])
Memo=["this is a memo"]

macro transaction(
		Journal_Entry,
		newdate,
		newdebit,
		debit_amount,
		newcredit, 
		credit_amount,
		new_memo)
	
	ex=quote
 	   if $credit_amount != $debit_amount
			"Error--Debit and Credit must match"
       else
			
				push!(spreadsheet,(defaultRow...,
					Accounts["Chequing"]=
						0,
					
						$newdebit = $debit_amount,
						$newcredit=$credit_amount,
						Date=$newdate,
						memo=$new_memo
						)) 


			#figure out how to delete this row, and get around repeat variables
			# I might have to save each transaction to the spreadsheet, at this stage
		end
	end
	esc(ex)
	
end

@transaction(JE3,"Jan 1, 2019", "Wages", 1000, "Chequing", 1000,"this is a test")

The problem is when I try to add the Journal entry

			$Journal_Entry=DataFrame(
				EntryDate=[$newdate,"","",""],
				Credited_Accounts=["", $newcredit,"",""],
				Debited_Accounts=["","",$newdebit,""],
				Credit=[0,$credit_amount,0,0],
				Debit=[0,0,$debit_amount,0],
				Balance=[0,0,0,0])

		end

I can get this to work, by having a string for newcredit and newdebit, but then the first part doesn’t work.

What I want is a function or macro, that is able to create a journal entry, and add a new value to my general ledger. Is there a way to have a CSV from a dictionary, and add the last value of a dictionary to a dataframe?

[sijo]

Let’s start with this piece of the puzzle:

Is there a way to have a CSV from a dictionary, and add the last value of a dictionary to a dataframe?

Please give a small but complete example of the input and the result that you want. Just the data, but completely specified (this is different from your example because in your example we don’t know what is JE3 and we don’t know what you expect as result; please show the result you want).

[brett_knoss]

OK

I have a DataFrame with one row
Income Expenses
Chequing Wages Sales Rent Personal Inputs Ducks
100 0 0 100 0 0 0 0 0 0 0 0 0 0
Each account has a debit and a credit (1st and 2nd respectively. I want it to give me a error if the sum of the credits does not equal the sum of the debits

Else

I wan it to add a row to the DataFrame, like this

Chequing Wages Sales Rent Personal Inputs Ducks
100 0 0 100 0 0 0 0 0 0 0 0 0 0
0 100 0 0 0 0 0 0 10 0 20 0 70 0

and create a Journal Entry as a DataFrame

Date Debited Accounts Credited Accounts Debit Credit
“Jan 1, 2000” “Chequing” “” 100 0
“” “” “Personal” 10
“” “” “Inputs” 0 20
“” “” “Ducks” 0 70
“” “Total” “Balance” 100 100

And I want to have all of this in one line of code.

JE3 Stands for Journal Entry 3 , the important thing here is that it gives an error if I repeat the Journal Entry. That way, it’s harder to add a row to the first DataFrame by mistake.

[brett_knoss]

Those are supposed to be aligned columns, maybe I’ll try to do it as a screenshot.

[xiaodai]

@brett_knoss

maybe I’ll try to do it as a screenshot.

A suggestion to make it better. Write the Julia code that would write the data into a CSV and is runnable.

E.g.

using CSV, DataFrames

df = DataFrame(col1 = [1,2,3], col2 =["def", "ghi", "abc"])

CSV.write("some_path.csv", df)

[brett_knoss]

	General_Ledger=DataFrame(Chequing=[0],Inputs=[0])
			
			JE3=DataFrame(
				EntryDate=[$newdate,"","",""],
				Credited_Accounts=["", "Chequing","",""],
				Debited_Accounts=["","","Inputs",""],
				Credit=[0,100,0,0],
				Debit=[0,0,100,0])

General_Ledger=DataFrame(Chequing=[0,100],Inputs=[0,100]

[brett_knoss]

macro transaction(
		journal_entry,
		newdate,
		newdebit,
		debit_amount,
		newcredit, 
		credit_amount,
		new_memo)
	
	ex=quote
 	   if $credit_amount != $debit_amount
			"Error--Debit and Credit must match"
       else
			
				push!(spreadsheet,(defaultRow...,
					$newcredit=$credit_amount,		
					$newdebit= $debit_amount,
					date=$newdate,
					memo=$new_memo
					)) 

		end
		
		$journal_entry=DataFrame(
			Date=[$newdate,"","",""],
			Credited_Accounts=["", String($newcredit),"",""],
			Debited_Accounts=["","",String($newdebit),""],
			Credit=[0,$credit_amount,0,0],
			Debit=[0,0,$debit_amount,0],
			Memo=["","","",$new_memo])
	end
	esc(ex)
	
end

@transaction(JE5,"Jan 1, 2019", wages, 1000, chequing, 1000, "this is a test")

This does everything I want, except save the name of an account as a string. I have to give each entry a new name, I need the debit and credit to balance. I just can’t have the name of the accounts.

[sijo]

Here’s a way to do it for what I understood of the task:

using DataFrames

# Initialize accounts data frame with one row
accounts = DataFrame(
    Chequing_debit=[100], Chequing_credit=[0],
    Wages_debit=[0], Wages_credit=[100],
    Sales_debit=[0], Sales_credit=[0],
    Rent_debit=[0], Rent_credit=[0],
    Personal_debit=[0], Personal_credit=[0],
    Inputs_debit=[0], Inputs_credit=[0],
    Ducks_debit=[0], Ducks_credit=[0],
)

# Initialize journal with 4 rows
journal = DataFrame(Date=["Jan 1, 2000" for _ in 1:4],
                    Debited_account=["Chequing", "", "", ""],
                    Credited_account=["", "Personal", "Inputs", "Ducks"],
                    Debit=[100, 0, 0, 0],
                    Credit=[0, 10, 20, 70],
)

The initial data frames look like this:

accounts:

 Row │ Chequing_debit  Chequing_credit  Wages_debit  Wages_credit  Sales_debit  Sales_credit  Rent_debit  Rent_credit  Personal_debit  Personal_credit  Inpu ⋯
     │ Int64           Int64            Int64        Int64         Int64        Int64         Int64       Int64        Int64           Int64            Int6 ⋯
─────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │            100                0            0           100            0             0           0            0               0                0       ⋯

journal:

 Row │ Date         Debited_account  Credited_account  Debit  Credit 
     │ String       String           String            Int64  Int64  
─────┼───────────────────────────────────────────────────────────────
   1 │ Jan 1, 2000  Chequing                             100       0
   2 │ Jan 1, 2000                   Personal              0      10
   3 │ Jan 1, 2000                   Inputs                0      20
   4 │ Jan 1, 2000                   Ducks                 0      70

Now a function to add a transaction:

function transaction!(date, debit_account, debit_amount, credit_account, credit_amount)
    if debit_amount != credit_amount
        error("Debit ($debit_amount) and credit ($credit_amount) must match")
    end

    # Prepare row as named tuple, starting with zero values overwritten by values for the specified accounts
    new_row = (; (Symbol.(names(accounts)) .=> 0)...,
                 Symbol(debit_account, "_debit") => debit_amount,
                 Symbol(credit_account, "_credit") => credit_amount)
    push!(accounts, new_row)

    push!(journal, (; Date=date, Debited_account=debit_account, Credited_account=credit_account,
                      Debit=debit_amount, Credit=credit_amount))
end

Test the function:

julia> transaction!("Jan 1, 2019", "Wages", 1000, "Chequing", 1000)
5×5 DataFrame
 Row │ Date         Debited_account  Credited_account  Debit  Credit 
     │ String       String           String            Int64  Int64  
─────┼───────────────────────────────────────────────────────────────
   1 │ Jan 1, 2000  Chequing                             100       0
   2 │ Jan 1, 2000                   Personal              0      10
   3 │ Jan 1, 2000                   Inputs                0      20
   4 │ Jan 1, 2000                   Ducks                 0      70
   5 │ Jan 1, 2019  Wages            Chequing           1000    1000

Note: this is all with integers… maybe you want to create the data frames with another number type.

[brett_knoss]

OK, I you did a function, then used symbol. That should allow me to do a function

[sijo]

Yes with a symbol and a pair => you can generate a named tuple where the keys are computed. For example instead of (; a=1, b=2) you can write

sym = :b
(; a=1, sym => 2)

By the way, to define the new row for the data frame you can also use a Dict instead of a named tuple (this way you can use strings for the keys instead of symbols). Replace

    new_row = (; (Symbol.(names(accounts)) .=> 0)...,
                 Symbol(debit_account, "_debit") => debit_amount,
                 Symbol(credit_account, "_credit") => credit_amount)

with

    new_row = Dict((names(accounts) .=> 0)...,
                 debit_account*"_debit" => debit_amount,
                 credit_account*"_credit" => credit_amount)

[brett_knoss]

function transaction!(date,journalentry, debit_account, debit_amount, credit_account, credit_amount)
    if debit_amount != credit_amount
        error("Debit ($debit_amount) and credit ($credit_amount) must match")
    end

    # Prepare row as named tuple, starting with zero values overwritten by values for the specified accounts
    new_row = (; (Symbol.(names(accounts)) .=> 0)...,
                 Symbol(debit_account, "_debit") => debit_amount,
                 Symbol(credit_account, "_credit") => credit_amount))
	
    push!(accounts, new_row)

   Symbol(journalentry)=DataFrame(Date=[newddate], 
		                          Symbol(debited_account)=[0],
		                          Symbol(credit_account)=[0],
		                          balance=[0])
	push!(journalentry, ( Date="", 
			               Symbol(debited_account)=debit_amount,
			               Symbol(credited_accoun)=0,
			               Balance=0))
	push!(journalentry, ( Date="", 
			               Symbol(debited_account)=debit_amount,
			               Symbol(credited_accoun)=0,
			               Balance=0))
end

I tried to change it so that I could create a new journal entry for each transaction, with a unique name, so that Pluto repeat transactions, but it says I have an extra “)”.

[oheil]

The extra ) is in new_row = .... Just remove the last one.

[sijo]

And here’s a simpler way, in case it’s OK to have missing instead of 0 values for accounts that are not touched by a transaction:

using DataFrames

accounts = DataFrame()
journal = DataFrame()

function transaction!(date, debit_account, debit_amount, credit_account, credit_amount)
    if debit_amount != credit_amount
        error("Debit ($debit_amount) and credit ($credit_amount) must match")
    end

    push!(accounts, cols=:union, Dict(debit_account*"_debit" => debit_amount,
                                      credit_account*"_credit" => credit_amount))

    push!(journal, (; date, debit_account, credit_account, debit_amount, credit_amount))
end

transaction!("Jan 1, 2019", "Wages", 1000, "Chequing", 1000)
transaction!("Jan 2, 2019", "Personal", 300, "Rent", 300)


julia> accounts
2×4 DataFrame
 Row │ Chequing_credit  Wages_debit  Rent_credit  Personal_debit 
     │ Int64?           Int64?       Int64?       Int64?         
─────┼───────────────────────────────────────────────────────────
   1 │            1000         1000      missing         missing 
   2 │         missing      missing          300             300

julia> journal
2×5 DataFrame
 Row │ date         debit_account  credit_account  debit_amount  credit_amount 
     │ String       String         String          Int64         Int64         
─────┼─────────────────────────────────────────────────────────────────────────
   1 │ Jan 1, 2019  Wages          Chequing                1000           1000
   2 │ Jan 2, 2019  Personal       Rent                     300            300

For the journal, columns are created implicitly when the first row is added. For accounts, columns are added as required every time a row is pushed that uses new accounts. With the keyword argument cols=:union we don’t need to give default values for the row: unspecified values are set to missing.