Annotation of pta/pta_import.1, Revision 1.8
1.8 ! freda 1: .\" $Id: pta_import.1,v 1.7 2020/11/25 15:04:25 freda Exp $
1.1 schwarze 2: .\"
3: .\" Copyright (c) 2020 Freda Bundchen
4: .\"
5: .\" Permission to use, copy, modify, and distribute this software for any
6: .\" purpose with or without fee is hereby granted, provided that the above
7: .\" copyright notice and this permission notice appear in all copies.
8: .\"
9: .\" THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
10: .\" WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
11: .\" MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
12: .\" ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
13: .\" WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
14: .\" ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
15: .\" OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
16: .\"
1.8 ! freda 17: .Dd $Mdocdate: November 25 2020 $
1.1 schwarze 18: .Dt PTA_IMPORT 1
19: .Os
20: .Sh NAME
21: .Nm pta_import
22: .Nd print pta journal entries from bank CSV file input
23: .Sh SYNOPSIS
24: .Nm pta_import
1.5 schwarze 25: .Fl I Ar accountname
1.2 schwarze 26: .Op Ar file
1.1 schwarze 27: .Sh DESCRIPTION
28: The
29: .Nm
1.2 schwarze 30: utility converts lines from a CSV
31: .Ar file
32: generated by a financial
1.1 schwarze 33: institution into journal entries used by
34: .Xr pta 1 .
1.2 schwarze 35: .Pp
36: If the
37: .Ar file
38: argument is omitted, standard input is read instead.
1.3 schwarze 39: .Pp
1.5 schwarze 40: .Ar accountname
1.3 schwarze 41: is exactly one of
1.4 schwarze 42: .Pp
43: .Bl -bullet -offset indent -compact
44: .It
1.6 freda 45: .Cm bbva_usa
46: .It
1.4 schwarze 47: .Cm capital_one_credit
48: .It
49: .Cm chase_credit
50: .It
51: .Cm optum_hsa
1.6 freda 52: .It
53: .Cm sparkasse_camt
54: .It
55: .Cm wellsfargo
1.3 schwarze 56: .El
57: .Pp
58: and corresponds to an existing
1.8 ! freda 59: .Pa ./import/ Ns Ar accountname Ns Pa .txt
! 60: configuration file.
1.5 schwarze 61: .Ss Configuration file format
1.8 ! freda 62: Each configuration file contains rules
1.7 freda 63: used to import transactions from a financial institution.
1.5 schwarze 64: Like all
65: .Xr pta 1
66: input files, it is an
67: .Xr ascii 7
68: text file using strings of one or more space characters
69: as the field delimiter and using the hash character
70: .Pq Sq #
71: in the first column to mark lines as ignored.
72: .Pp
1.7 freda 73: The rules are as follows:
74: .Bl -tag -width Ds
75: .It Ic ACCOUNT Ar csv_account_number
76: The number in
77: .Xr pta-accounts 5
78: of the bank or credit card account this CSV file contains entries for.
79: This rule is required in every configuration file.
80: .It Ic DELIM Ar delimiter
81: The character used to separate fields.
82: This rule is required in every configuration file.
83: Most financial institutions use commas but some use semicolons.
84: .It Ic HEADER Ar header
85: Skip the first line of the CSV file if it matches the
86: .Ar header
87: line verbatim, or cause parsing to fail if it does not.
88: .It Ic IGNORE Ar regular_expression
89: Skip all lines in the CSV file matching the
90: .Ar regular_expression .
91: .It Ic QUOTE Ar quote
92: Expects that every field starts and ends with the
93: .Ar quote
94: character.
95: .It Ar selector account_number booking_identifier
96: Lines in the CSV file that match the
1.5 schwarze 97: .Ar selector
1.7 freda 98: are associated with the contra account
1.5 schwarze 99: .Ar account_number
1.7 freda 100: and are assigned the
101: .Ar booking_identifier .
102: The selector is a string consisting of regular expressions separated
103: by delimiters defined in the
104: .Ic DELIM
105: rule.
106: .El
1.1 schwarze 107: .Sh FILES
1.4 schwarze 108: .Bl -tag -width Ds -compact
1.8 ! freda 109: .It Pa ./import/bbva_usa.txt
! 110: .It Pa ./import/capital_one_credit.txt
! 111: .It Pa ./import/chase_credit.txt
! 112: .It Pa ./import/sparkasse_camt.txt
! 113: .It Pa ./import/optum_hsa.txt
! 114: .It Pa ./import/wellsfargo.txt
1.1 schwarze 115: .El
1.3 schwarze 116: .Pp
117: These files contain the header that the financial institution uses, the
118: account number from
119: .Pa ./accounts.txt
120: used by this account, as well as patterns used to map transactions to
121: the correct account and booking identifier.
1.1 schwarze 122: .Sh EXIT STATUS
123: .Ex -std
124: .Sh EXAMPLES
125: Add journal entries to a journal file using a CSV file generated by
126: a financial institution:
127: .Pp
1.3 schwarze 128: .Dl "$ pta_import -I chase_credit chasedownload.csv >> journal.txt"
1.5 schwarze 129: .Ss Account numbers in the configuration file
130: The line
1.1 schwarze 131: .Pp
1.5 schwarze 132: .Dl ACCOUNT 3235
1.3 schwarze 133: .Pp
1.5 schwarze 134: in the configuration file corresponds to a line such as
1.3 schwarze 135: .Pp
1.5 schwarze 136: .Dl 3235 L Capital One credit card
1.3 schwarze 137: .Pp
138: in
1.5 schwarze 139: .Pa ./accounts.txt .
1.1 schwarze 140: .Pp
1.3 schwarze 141: The description
142: .Qq Capital One credit card
143: may vary, but in this example, the account numbers
1.5 schwarze 144: .Qq 3235
1.3 schwarze 145: match, and the account type is
146: .Qq L
147: to indicate that it is a liability account.
1.5 schwarze 148: .Ss Patterns in the configuration file
1.3 schwarze 149: Suppose
1.4 schwarze 150: .Pp
1.3 schwarze 151: .Dl ,,,Automotive,,, 1111 AUTO
152: .Pp
153: is a sample entry in the
1.8 ! freda 154: .Pa ./import/chase_credit.txt
1.3 schwarze 155: file which uses headers of the form
156: .Pp
157: .Dl Transaction Date,Post Date,Description,Category,Type,Amount
158: .Pp
159: Then this
160: .Ar selector
161: ignores the transaction date, post date, description, type, and amount.
162: It matches the
163: .Qq Automotive
164: category and maps transactions to account
165: .Qq 1111
166: defined in
167: .Pa ./accounts.txt
168: with the
169: .Qq Auto
170: booking identifier.
1.5 schwarze 171: .Ss A sample credit card purchase
172: If
173: .Pa chasedownload.csv
174: file contains
175: .Pp
176: .Dl "10/26/2020,10/26/2020,example.com*asldfkj,Shopping,Sale,-17.07"
177: .Pp
178: and
1.8 ! freda 179: .Pa ./import/chase_credit.txt
1.5 schwarze 180: contains
181: .Bd -literal -offset Ds
182: ACCOUNT 3234
183: ,,,Shopping,,, 9996 SHOP
184: .Ed
185: .Pp
186: and
187: .Pa ./accounts.txt
188: contains
189: .Bd -literal -offset Ds
190: 3234 L Chase credit card
191: 9996 X shopping
192: .Ed
193: .Pp
194: then the
195: .Qq Shopping
196: category defined by Chase is mapped to the user's
197: .Qq SHOP
198: booking identifier, the user's account for shopping
199: expenses is defined as
200: .Qq 9996
201: in their
202: .Pa ./accounts.txt ,
203: the user's account for the credit card is 3234 in their
204: .Pa ./accounts.txt ,
205: and
206: .Nm
207: prints
208: .Pp
209: .Dl "20201026 SHOP 9996 3234 17.07 example.com*asldfkj"
1.3 schwarze 210: .Ss A complete example
211: Suppose
212: .Pa ./accounts.txt
213: is given by
214: .Bd -literal
215: 1700 A My Bank
216: 3234 L My Chase Credit Card
217: 5102 X Physician expenses
218: 5103 X Dental expenses
219: 5203 X Shopping
220: .Ed
1.1 schwarze 221: .Pp
1.3 schwarze 222: and
223: .Pa chasedownload.csv
224: is given by
225: .Bd -literal
226: Transaction Date,Post Date,Description,Category,Type,Amount,Memo
227: 11/06/2020,11/09/2020,Dr. Joseph Schmoe,Health & Wellness,Sale,-100.00,
228: 11/06/2020,11/09/2020,Dr. Sally Sue,Health & Wellness,Sale,-183.00,
229: 11/08/2020,11/08/2020,example.com,Shopping,Sale,-53.83,
230: 11/04/2020,11/04/2020,Payment Thank You - Web,,Payment,75.43,
231: .Ed
1.1 schwarze 232: .Pp
1.3 schwarze 233: and
1.8 ! freda 234: .Pa ./import/chase_credit.txt
1.3 schwarze 235: is given by
236: .Bd -literal
237: # Chase credit card header is
238: # Transaction Date,Post Date,Description,Category,Type,Amount
239: # The format of this file is
240: # selector account_number booking_identifier
241: # selector is a comma-separated list of regular expressions which
242: # matches corresponding fields in the CSV file
243: ACCOUNT 3234
244: ,,,Automotive,,, 1111 AUTO
245: ,,,Bills & Utilities,,, 2222 BILLS
246: ,,,Fees & Adjustments,,, 3333 FEES
247: ,,,Food & Drink,,, 4444 FOOD
248: ,,,Gas,,, 5555 GAS
249: ,,,Gifts & Donations,,, 6666 GIFTS
250: ,,,Groceries,,, 7777 GROCERIES
251: ,,Dr. Sally Sue,,,, 5103 DENTAL
252: ,,,Health & Wellness,,, 5102 PHYSICIAN
253: ,,,Home,,, 9999 HOME
254: ,,,Personal,,, 9998 PERSONAL
255: ,,,Professional Services,,, 9997 PRO
256: ,,,Shopping,,, 5203 SHOP
257: ,,,,Payment,, 1700 PAY
258: .Ed
1.1 schwarze 259: .Pp
260: Then
1.4 schwarze 261: .Pp
1.3 schwarze 262: .Dl pta_import -I chase_credit chasedownload.csv
1.4 schwarze 263: .Pp
1.3 schwarze 264: outputs
265: .Bd -literal
266: 20201109 PHYSICIAN 5102 3234 100.00 Dr. Joseph Schmoe
267: 20201109 DENTAL 5103 3234 183.00 Dr. Sally Sue
268: 20201108 SHOP 5203 3234 53.83 example.com
269: 20201104 PAY 3234 1700 75.43 Payment Thank You - Web
270: .Ed
1.5 schwarze 271: .Pp
272: Note that the selector for Dr\&. Sally Sue
273: in the example configuration file comes before the
274: Health & Wellness selector.
275: Since the first match wins, transactions for Dr\&. Sally Sue
276: can be classified with the DENTAL booking identifier,
277: even though the bank's CSV file classifies
278: these transactions as Health & Wellness.
1.1 schwarze 279: .Sh SEE ALSO
280: .Xr pta-accounts 5 ,
281: .Xr pta-journal 5 ,
282: .Xr pta-glossary 7
283: .Sh BUGS
284: Currently,
285: .Nm
286: has the following limitations:
287: .Bl -dash
288: .It
1.3 schwarze 289: Pattern recognition in the
1.8 ! freda 290: .Pa import/accountname.txt
1.3 schwarze 291: files is under continued development.
292: .It
1.6 freda 293: Only CSV files for these account types are supported:
294: .Bl -bullet -offset indent -compact
295: .It
296: .Cm BBVA USA bank accounts
297: .It
298: .Cm Capital One credit cards
299: .It
300: .Cm Chase credit cards
301: .It
302: .Cm Optum HSA bank accounts
303: .It
304: .Cm Sparkasse bank accounts
305: .It
306: .Cm Wells Fargo bank accounts
307: .El
1.1 schwarze 308: .El
CVSweb