Annotation of pta/pta_import.1, Revision 1.12
1.12 ! freda 1: .\" $Id: pta_import.1,v 1.11 2020/11/30 17:45:34 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.11 freda 17: .Dd $Mdocdate: November 30 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.9 freda 47: .Cm capital_one_360
48: .It
1.4 schwarze 49: .Cm capital_one_credit
50: .It
51: .Cm chase_credit
52: .It
1.9 freda 53: .Cm etrade_ira
54: .It
1.4 schwarze 55: .Cm optum_hsa
1.6 freda 56: .It
57: .Cm sparkasse_camt
58: .It
59: .Cm wellsfargo
1.3 schwarze 60: .El
61: .Pp
62: and corresponds to an existing
1.8 freda 63: .Pa ./import/ Ns Ar accountname Ns Pa .txt
64: configuration file.
1.5 schwarze 65: .Ss Configuration file format
1.8 freda 66: Each configuration file contains rules
1.7 freda 67: used to import transactions from a financial institution.
1.5 schwarze 68: Like all
69: .Xr pta 1
70: input files, it is an
71: .Xr ascii 7
72: text file using strings of one or more space characters
73: as the field delimiter and using the hash character
74: .Pq Sq #
75: in the first column to mark lines as ignored.
76: .Pp
1.7 freda 77: The rules are as follows:
78: .Bl -tag -width Ds
79: .It Ic ACCOUNT Ar csv_account_number
80: The number in
81: .Xr pta-accounts 5
82: of the bank or credit card account this CSV file contains entries for.
83: This rule is required in every configuration file.
1.11 freda 84: .It Ic AMOUNT Oo Ar debit_column Oc Ar credit_column
85: The column numbers in the CSV file containing amounts to be
86: booked on the debit or credits side of the contra account
87: .Ar account_number ,
88: respectively.
89: If
90: .Ar debit_column
91: is omitted, amounts preceded by a minus sign or enclosed
92: in parentheses are booked to the debit side.
1.10 freda 93: .It Ic COSTCENTER Ar costcenter_column
94: The column number in the CSV file containing the cost center.
95: Numbering starts from 1.
96: If this rule is not specified, no cost centers are assigned.
97: .It Ic DATE Ar date_column date_format
98: The column number in the CSV file containing the date.
99: Numbering starts from 1.
100: The
101: .Ar date_format
102: is one of
103: .Ic MM/DD/YY ,
104: .Ic MM/DD/YYYY ,
105: .Ic YYYY-MM-DD ,
106: or
107: .Ic DD.MM.YY .
108: This rule is required in every configuration file.
1.11 freda 109: .It Ic DESCRIPTION Ar descr_column ...
110: The column numbers in the CSV file to be concatenated to form the
111: text string for the journal entry.
112: Numbering starts from 1.
1.7 freda 113: .It Ic DELIM Ar delimiter
114: The character used to separate fields.
115: This rule is required in every configuration file.
116: Most financial institutions use commas but some use semicolons.
117: .It Ic HEADER Ar header
118: Skip the first line of the CSV file if it matches the
119: .Ar header
120: line verbatim, or cause parsing to fail if it does not.
121: .It Ic IGNORE Ar regular_expression
122: Skip all lines in the CSV file matching the
123: .Ar regular_expression .
1.10 freda 124: .It Ic QUANTITY Ar quantity_column
125: The column number in the CSV file containing the number of stock shares.
126: Numbering starts from 1.
127: If this rule is not specified, no information about the number of traded
128: securities is appended to the text string.
1.7 freda 129: .It Ic QUOTE Ar quote
130: Expects that every field starts and ends with the
131: .Ar quote
132: character.
133: .It Ar selector account_number booking_identifier
134: Lines in the CSV file that match the
1.5 schwarze 135: .Ar selector
1.7 freda 136: are associated with the contra account
1.5 schwarze 137: .Ar account_number
1.7 freda 138: and are assigned the
139: .Ar booking_identifier .
140: The selector is a string consisting of regular expressions separated
141: by delimiters defined in the
142: .Ic DELIM
143: rule.
144: .El
1.1 schwarze 145: .Sh FILES
1.12 ! freda 146: Example configuration files:
! 147: .Bl -tag -offset indent -width Ds -compact
1.8 freda 148: .It Pa ./import/bbva_usa.txt
1.9 freda 149: .It Pa ./import/capital_one_360.txt
1.8 freda 150: .It Pa ./import/capital_one_credit.txt
151: .It Pa ./import/chase_credit.txt
1.9 freda 152: .It Pa ./import/etrade_ira.txt
153: .It Pa ./import/optum_hsa.txt
1.8 freda 154: .It Pa ./import/sparkasse_camt.txt
155: .It Pa ./import/wellsfargo.txt
1.1 schwarze 156: .El
1.3 schwarze 157: .Pp
1.12 ! freda 158: Example CSV files:
! 159: .Bl -tag -offset indent -width Ds -compact
! 160: .It Pa ./csv/bbva_usa.csv
! 161: .It Pa ./csv/capital_one_360.csv
! 162: .It Pa ./csv/capital_one_credit.csv
! 163: .It Pa ./csv/chase_credit.csv
! 164: .It Pa ./csv/etrade_ira.csv
! 165: .It Pa ./csv/optum_hsa.csv
! 166: .It Pa ./csv/wellsfargo.csv
! 167: .El
1.1 schwarze 168: .Sh EXIT STATUS
169: .Ex -std
170: .Sh EXAMPLES
171: Add journal entries to a journal file using a CSV file generated by
172: a financial institution:
173: .Pp
1.3 schwarze 174: .Dl "$ pta_import -I chase_credit chasedownload.csv >> journal.txt"
1.5 schwarze 175: .Ss Account numbers in the configuration file
176: The line
1.1 schwarze 177: .Pp
1.5 schwarze 178: .Dl ACCOUNT 3235
1.3 schwarze 179: .Pp
1.5 schwarze 180: in the configuration file corresponds to a line such as
1.3 schwarze 181: .Pp
1.5 schwarze 182: .Dl 3235 L Capital One credit card
1.3 schwarze 183: .Pp
184: in
1.5 schwarze 185: .Pa ./accounts.txt .
1.1 schwarze 186: .Pp
1.3 schwarze 187: The description
188: .Qq Capital One credit card
189: may vary, but in this example, the account numbers
1.5 schwarze 190: .Qq 3235
1.3 schwarze 191: match, and the account type is
192: .Qq L
193: to indicate that it is a liability account.
1.5 schwarze 194: .Ss Patterns in the configuration file
1.3 schwarze 195: Suppose
1.4 schwarze 196: .Pp
1.3 schwarze 197: .Dl ,,,Automotive,,, 1111 AUTO
198: .Pp
199: is a sample entry in the
1.8 freda 200: .Pa ./import/chase_credit.txt
1.3 schwarze 201: file which uses headers of the form
202: .Pp
203: .Dl Transaction Date,Post Date,Description,Category,Type,Amount
204: .Pp
205: Then this
206: .Ar selector
207: ignores the transaction date, post date, description, type, and amount.
208: It matches the
209: .Qq Automotive
210: category and maps transactions to account
211: .Qq 1111
212: defined in
213: .Pa ./accounts.txt
214: with the
215: .Qq Auto
216: booking identifier.
1.5 schwarze 217: .Ss A sample credit card purchase
218: If
219: .Pa chasedownload.csv
220: file contains
221: .Pp
222: .Dl "10/26/2020,10/26/2020,example.com*asldfkj,Shopping,Sale,-17.07"
223: .Pp
224: and
1.8 freda 225: .Pa ./import/chase_credit.txt
1.5 schwarze 226: contains
227: .Bd -literal -offset Ds
228: ACCOUNT 3234
229: ,,,Shopping,,, 9996 SHOP
230: .Ed
231: .Pp
232: and
233: .Pa ./accounts.txt
234: contains
235: .Bd -literal -offset Ds
236: 3234 L Chase credit card
237: 9996 X shopping
238: .Ed
239: .Pp
240: then the
241: .Qq Shopping
242: category defined by Chase is mapped to the user's
243: .Qq SHOP
244: booking identifier, the user's account for shopping
245: expenses is defined as
246: .Qq 9996
247: in their
248: .Pa ./accounts.txt ,
249: the user's account for the credit card is 3234 in their
250: .Pa ./accounts.txt ,
251: and
252: .Nm
253: prints
254: .Pp
255: .Dl "20201026 SHOP 9996 3234 17.07 example.com*asldfkj"
1.3 schwarze 256: .Ss A complete example
257: Suppose
258: .Pa ./accounts.txt
259: is given by
260: .Bd -literal
261: 1700 A My Bank
262: 3234 L My Chase Credit Card
263: 5102 X Physician expenses
264: 5103 X Dental expenses
265: 5203 X Shopping
266: .Ed
1.1 schwarze 267: .Pp
1.3 schwarze 268: and
269: .Pa chasedownload.csv
270: is given by
271: .Bd -literal
272: Transaction Date,Post Date,Description,Category,Type,Amount,Memo
273: 11/06/2020,11/09/2020,Dr. Joseph Schmoe,Health & Wellness,Sale,-100.00,
274: 11/06/2020,11/09/2020,Dr. Sally Sue,Health & Wellness,Sale,-183.00,
275: 11/08/2020,11/08/2020,example.com,Shopping,Sale,-53.83,
276: 11/04/2020,11/04/2020,Payment Thank You - Web,,Payment,75.43,
277: .Ed
1.1 schwarze 278: .Pp
1.3 schwarze 279: and
1.8 freda 280: .Pa ./import/chase_credit.txt
1.3 schwarze 281: is given by
282: .Bd -literal
283: # Chase credit card header is
284: # Transaction Date,Post Date,Description,Category,Type,Amount
285: # The format of this file is
286: # selector account_number booking_identifier
287: # selector is a comma-separated list of regular expressions which
288: # matches corresponding fields in the CSV file
289: ACCOUNT 3234
290: ,,,Automotive,,, 1111 AUTO
291: ,,,Bills & Utilities,,, 2222 BILLS
292: ,,,Fees & Adjustments,,, 3333 FEES
293: ,,,Food & Drink,,, 4444 FOOD
294: ,,,Gas,,, 5555 GAS
295: ,,,Gifts & Donations,,, 6666 GIFTS
296: ,,,Groceries,,, 7777 GROCERIES
297: ,,Dr. Sally Sue,,,, 5103 DENTAL
298: ,,,Health & Wellness,,, 5102 PHYSICIAN
299: ,,,Home,,, 9999 HOME
300: ,,,Personal,,, 9998 PERSONAL
301: ,,,Professional Services,,, 9997 PRO
302: ,,,Shopping,,, 5203 SHOP
303: ,,,,Payment,, 1700 PAY
304: .Ed
1.1 schwarze 305: .Pp
306: Then
1.4 schwarze 307: .Pp
1.3 schwarze 308: .Dl pta_import -I chase_credit chasedownload.csv
1.4 schwarze 309: .Pp
1.3 schwarze 310: outputs
311: .Bd -literal
312: 20201109 PHYSICIAN 5102 3234 100.00 Dr. Joseph Schmoe
313: 20201109 DENTAL 5103 3234 183.00 Dr. Sally Sue
314: 20201108 SHOP 5203 3234 53.83 example.com
315: 20201104 PAY 3234 1700 75.43 Payment Thank You - Web
316: .Ed
1.5 schwarze 317: .Pp
318: Note that the selector for Dr\&. Sally Sue
319: in the example configuration file comes before the
320: Health & Wellness selector.
321: Since the first match wins, transactions for Dr\&. Sally Sue
322: can be classified with the DENTAL booking identifier,
323: even though the bank's CSV file classifies
324: these transactions as Health & Wellness.
1.1 schwarze 325: .Sh SEE ALSO
326: .Xr pta-accounts 5 ,
327: .Xr pta-journal 5 ,
328: .Xr pta-glossary 7
329: .Sh BUGS
330: Currently,
331: .Nm
332: has the following limitations:
333: .Bl -dash
334: .It
1.3 schwarze 335: Pattern recognition in the
1.8 freda 336: .Pa import/accountname.txt
1.3 schwarze 337: files is under continued development.
338: .It
1.9 freda 339: User-defined configuration files are not currently
340: supported.
1.1 schwarze 341: .El
CVSweb
![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to pta_import.1 CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [cvsweb.bsd.lv] / pta