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


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

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.

4 Likes

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

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)
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 “)”.

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

1 Like

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.

I fixed that, but now it’s saying that I can’t have have Symbol(debit_account) as the name of a column

 Symbol(journalentry)=DataFrame(Date=[newddate], 
		                          Symbol(debit_account)=[0],
		                          Symbol(credit_account)=[0],
		                          balance=[0])

I think I have it fixed, but I can’t test it. How do I do push! if I have a DataFrame or DataFrames?

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], 
		                          Debited_Account=[""],
		                          Credited_Account=[""],
								  Credit=[0],
								  Debit=[0],
		                          Balance=[0])
	push!(journalentry, ( Date="",
						   Debited_Account=debit_account,
			               Credited_Account=0,
			               Debit=debit_amount,
			               Credit=0,
			               Balance=0))
push!(journalentry, ( Date="",
						   Debited_Account="",
			               Credited_Account=credit_account,
			               Debit=0,
			               Credit=credit_ammount,
			               Balance=0))
end

I want a new dataframe for each journal entry

I know we keep telling you to read the documentation, but this line should be a red flag. Here you are re-defining the function Symbol(.), not constructing a data frame.

If you don’t understand why this syntax is doing that, please consult this section of the documentation.

5 Likes

I’m not finding any heading refrerencing Symbol(.)

How do I fix it?

I keep going in circles, do I use a macro, or do I use a function? How do I a get this


as a dataframe

and this
newstyletransactions2
as a dataframe

with one line of code.

The line

Symbol(journalentry)=...

defines a function, named Symbol, with a parameter journalentry. It returns a DataFrame in your case.
It’s not clear, what this line really should do, but surely not defining a function.

Well, I admit, I’m a bit lost in all these things. Perhaps we start again from a more clean beginning? We should not start with all the code you already have, because it already seems to be a bit confusing, at least to me. Perhaps it’s better to start with a clean table, and a minimal task to achieve. From where you can go further alone? I think it’s important, that you understand the underlying principles. Just providing you with working code is not what you really need. What do you think? Is this reasonable? Or is there just a single link missing I don’t see?

4 Likes

Probably best to start over. Although can I use $journal_entry in a journal?

ok, please start a new thread, with a minimal task. Your above screenshots a good start, together with some explanation. No code for now, or only minimal, like DataFrame initializations. Perhaps it would be nice, to know something about your background, like coming from R/Mathlab/SAS or similar, programming experience,… e.g.
And it would be good to know, in which environment you want to work, like: Pluto (I think you mentioned it somewhere).

Some R and SPSS, most of my experience is in Julia. My problem here is with how I’m using variables. I have practiced, and did take a computer class in Python, that went over the basic data types.

These examples are from different sources, but the ledger(bottom) has each account as a title. The journal(top) documents one transaction (a row in the ledger), with the Name of each account(the columns of the ledger) that is modified in the transaction.

To process a transaction the sums of credit and debit must match.

I created a new thread
Creating an Accounting Program in Julia, for Pluto