Skip to content

Latest commit

 

History

History
210 lines (153 loc) · 8.28 KB

0205_querying.md

File metadata and controls

210 lines (153 loc) · 8.28 KB
Raw

Querying Your Transactions

Querying Accounts {#sec:querying_accounts}

A bare balance or register report is great for seeing all data. However, it's more realistic to limit output in some way in order to see transactions related to other transations or accounts related to others in some way. ledger has a very powerful query language, the key tenets of which you'll learn in these next few sections

Perhaps you want to only see your assets on hand. Run a balance report and specify Assets with ledger -f ex.ledger balance Assets to see only Assets. @Lst:examples_assets shows the output.

Listing: Assets-only report on @lst:examples {#lst:examples_assets}

ledger -f ex.ledger balance Assets

::: tryit

TRY IT: Try some other accounts present in ex.ledger to see the balance for top-level accounts as well as subaccounts.

:::

If you're not sure what accounts are available to be queried, ledger can help. To see the accounts present in a transaction log, use the accounts commmand in place of balance or register. You will see something like @lst:example_accounts.

Listing: ledger -f ex.ledger accounts output {#lst:example_accounts}

ledger -f ex.ledger accounts

Important Balance Report Types

In the introductory talk you watched as a part of @sec:intro_talk, you learned about two important report types: Cashflow (also called Profit and Loss) and Net Worth.

Cashflow {#sec:cashflow}

Cashflow tracks Income and Expenses: money that came in from outside of your control and money that exited your control. Subtract Expenses from Income and you will know if you had a gain and turned a profit or experienced a loss.

ledger makes a cashflow report incredible easy. The query is simply Income Expenses! Try it out on ex.ledger. See @lst:example_cashflow for the expected output of ledger -f ex.ledger bal Income Expenses.

Listing: A cashflow report generated by ledger -f ex.ledger bal Income Expenses {#lst:example_cashflow}

ledger -f ex.ledger bal Income Expenses

Net Worth {#sec:networth}

Net Worth tracks Assets and Liabilities: money that you control and money owed to someone else. Subtract Liabilities from Assets and you will ascertain your current net worth. It's OK if this number is negative: it just means that you owe more than you currently have. When tracking your net work for real, don't forget to track real property, like a house or a car. A house is an asset that has a corresponding liability that goes away over time: a mortgage! You'll see how to track house value, mortgage, and associated expenses in @sec:commodity_home.

Like the cashflow report, ascertaining net worth is very easy in ledger. The query is simply Assets Liabilities. See @lst:example_networth for the expected output of ledger -f ex.ledger bal Assets Liabilities.

Listing: A net worth report generated by ledger -f ex.ledger bal Assets Liabilities. {#lst:example_networth}

ledger -f ex.ledger bal Assets Liabilities

Querying Payees {#sec:querying_payees}

When you want to find all of the transactions for a certain payee, ledger has a special query keyword for it: payee. Using payee is common way to find transactions with a business where you might have used different methods of payment, like cash on one visit but a debit or credit card the next. @Lst:payee_query shows this.

Listing: A register of Commonplace Coffee purchases ledger -f ex.ledger reg payee "Commonplace Coffee" {#lst:payee_query}

ledger -f ex.ledger reg payee "Commonplace Coffee"

::: tryit

TRY IT: Try finding the other payees through a payee query.

:::

Querying Tagged Transactions {#sec:querying_tagged_transactions}

Tagged transactions are a powerful way to set simple key-value pairs of metadata associated with a transaction. For example, you could track the contact information of payees or maybe a warranty expiration date for something you purchased.

Listing: A transaction with a tag comment (tx_tags.ledger) {#lst:tagged_tx}

2020-06-29 Costco
  ; warranty_expiration: 2023-06-29
  Expenses:Electronics:Computer   1,200.00 USD
  Expenses:Groceries                200.00 USD
  Liabilities:CreditCard:Visa

2020-07-01 Costco
  Expenses:Groceries      100.00 USD
  Liabilities:CreditCard:Visa

Using the register and print commands, we can see just the transactions that have a tag (@lst:warranty_tag) or match based on tag value (@lst:warranty_tag_value).

Listing: A register of transactions that have the warranty_expiration tag with ledger -f tx_tags.ledger reg tag warranty_expiration {#lst:warranty_tag}

ledger -f tx_tags.ledger reg tag warranty_expiration

Listing: A print of transactions that have the warranty_expiration tag with ledger -f tx_tags.ledger print tag warranty_expiration=2023-06-29 {#lst:warranty_tag_value}

ledger -f tx_tags.ledger print tag warranty_expiration=2023-06-29

Limiting by Date

For the last set of basic reports, it's important to learn limiting by date: setting a beginning of the report, an end, and how to group within a specified time period. For instance, you might want to know how much you spent on coffee for June and July 2017.

--begin and --end take a date-like string, such as 2017/06/30 or June 2017, or a relative date such as last July. --begin is inclusive and --end is exclusive. That is, if you specify 2020/07/24 as the end date, transactions dated 2020/07/23 will be included but not those dated 2020/07/24.

ledger supports a few grouping shortcuts for time periods: --daily, --weekly, --monthly, --quarterly, and --yearly. These are useful for showing the register for these time periods in order to see changes within that time period.

Listing: A date-limited, monthly query: ledger -f ex.ledger --begin 'June 2017' --end 'August 2017' --monthly reg 'Coffee$' {#lst:basic_date_query}

ledger -f ex.ledger --begin 'June 2017' --end 'August 2017' --monthly reg 'Coffee$'

One more thing: notice the $ in the query. ledger supports regular expressions (regex) for queries. In fact, it uses it by default. If you're familiar with regex, you'll understand this expression. Searching Coffee is actually matching accounts .*Coffee.* but the inclusion of another regex marker, such as the $ which indicates to only look at the end of a line or ^, which only matches from the beginning, changes that default.

::: tryit

TRY IT: create a transaction that includes the word "Assets" in a posting's account, but not at the beginning. Try "Expenses:Assets". Now, try the cashflow report as described in @sec:cashflow. What happened? What can you do to fix the report so that it will work with this account name in your records?

:::

::: protip

PROTIP: Avoid using the top-level accounts anywhere in a subaccount. That is, the words Assets, Expenses, Income, Liabilities, and Equity should only ever appear at the beginning of account names. If you don't do this, you will have to use ^ in your queries involving these top-level accounts. It's not a bad idea to do that anyway!

:::

Sorting

Nearly all queries can be sorted in some way. Sorting print or register with --sort d will sort by date. Sorting balance with --sort T sorts by the absolute value of the total for each account.

ledger's --sort option actually takes what's called a VEXPR, or a value expression. The ledger docs on value expressions are quite in-depth, but @tbl:common_sorts contains some of the most common ones.

Table: Common value expression variables used for simple sorting keys {#tbl:common_sorts}

Variable Purpose
d A posting’s date, as the number of seconds past the epoch. This is always “today” for an account.
a The posting’s amount; the balance of an account, without considering children.
v The market value of a posting or an account, without its children.
X '1' if a posting’s transaction has been cleared, '0' otherwise.