[GNC] CSV to QIF for Citibank

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

[GNC] CSV to QIF for Citibank

Michael DeBusk-2
Some time back, Citibank decided it would stop offering its transactions in
QIF format. I edited the CSV in vim (I only had a few transactions) until
this past week, when I realized I can't be a good Linux nerd unless I
automate repetitive tasks.

This is my attempt at an awk script specific to Citibank's file format. The
QIF needs some editing after it's done, but it'll save me a lot of time
until they change the format again.

(I'd appreciate criticism from awk masters on the list.)

#!/usr/bin/awk -f
BEGIN {
    FS=","
    print "!Account"
    print "NLiabilities:Citibank:Costco Anywhere Visa" # Change to your
account
    print "TCCard"
    print "\\Statement Balance Date"
    print "$Statement Balance"
    print "^"
    print "!Type:CCard"
}
FNR == 1 { next }
{
    print "D"$2
    if ($3~/AUTOPAY/) {
        print "PCitibank";
        print "T"sqrt($5^2); # This gets rid of the "-" before the amount
        print "LAssets:Current Assets:"; # change to your checking account
        print "MAutopay";
    }
    else {
        print "P"tolower(substr($3, 2, length($3)-2)); # This gets rid of
the double quotes around this string, and lower-case it
        print "T-"$4;
        print "LExpenses:&&";
        print "M&&";
    }
print "^"
}

--
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Greg Feneis
Not what you asked for, but what alternative file types does Citibank
provide besides CSV?

Kind regards, Greg Feneis
(Pixel 3)


On Sun, Jun 16, 2019, 22:17 Michael DeBusk <[hidden email]> wrote:

> Some time back, Citibank decided it would stop offering its transactions in
> QIF format. I edited the CSV in vim (I only had a few transactions) until
> this past week, when I realized I can't be a good Linux nerd unless I
> automate repetitive tasks.
>
> This is my attempt at an awk script specific to Citibank's file format. The
> QIF needs some editing after it's done, but it'll save me a lot of time
> until they change the format again.
>
> (I'd appreciate criticism from awk masters on the list.)
>
> #!/usr/bin/awk -f
> BEGIN {
>     FS=","
>     print "!Account"
>     print "NLiabilities:Citibank:Costco Anywhere Visa" # Change to your
> account
>     print "TCCard"
>     print "\\Statement Balance Date"
>     print "$Statement Balance"
>     print "^"
>     print "!Type:CCard"
> }
> FNR == 1 { next }
> {
>     print "D"$2
>     if ($3~/AUTOPAY/) {
>         print "PCitibank";
>         print "T"sqrt($5^2); # This gets rid of the "-" before the amount
>         print "LAssets:Current Assets:"; # change to your checking account
>         print "MAutopay";
>     }
>     else {
>         print "P"tolower(substr($3, 2, length($3)-2)); # This gets rid of
> the double quotes around this string, and lower-case it
>         print "T-"$4;
>         print "LExpenses:&&";
>         print "M&&";
>     }
> print "^"
> }
>
> --
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Michael DeBusk-2
On Mon, Jun 17, 2019, 01:55 Greg Feneis <[hidden email]> wrote:

> Not what you asked for, but what alternative file types does Citibank
> provide besides CSV?
>

They also offer QFX, OFX, tab-delimited text (which, in retrospect, I wish
I'd used), and one other I can't recall.
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Michael DeBusk-2
On Mon, Jun 17, 2019, 09:39 Kenneth Marshall <[hidden email]> wrote:

I think you need to subscribe to their business service to get anything
> other than the CSV file type. Please correct me if I am mistaken because
> that would be great news.
>

I don't subscribe to their business service, and I'm offered multiple file
formats. Perhaps it's because I'm using their Costco card.
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Greg Feneis
Is it too late to download the records of interest in QFX or OFX format?
GnuCash will import these formats as well.

Kind regards, Greg Feneis
(Pixel 3)


On Mon, Jun 17, 2019, 06:51 Michael DeBusk <[hidden email]> wrote:

> On Mon, Jun 17, 2019, 09:39 Kenneth Marshall <[hidden email]> wrote:
>
> I think you need to subscribe to their business service to get anything
> > other than the CSV file type. Please correct me if I am mistaken because
> > that would be great news.
> >
>
> I don't subscribe to their business service, and I'm offered multiple file
> formats. Perhaps it's because I'm using their Costco card.
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Michael DeBusk-2
On Mon, Jun 17, 2019 at 12:49 PM Greg Feneis <[hidden email]> wrote:

> Is it too late to download the records of interest in QFX or OFX format?
> GnuCash will import these formats as well.
>

It's not too late, but I never learned to appreciate QFX or OFX.
--
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

GnuCash - User mailing list
In reply to this post by Michael DeBusk-2
On 6/17/19 6:48 AM, Michael DeBusk wrote:
> On Mon, Jun 17, 2019, 09:39 Kenneth Marshall <[hidden email]> wrote:
>
> I think you need to subscribe to their business service to get anything
>> other than the CSV file type. Please correct me if I am mistaken because
>> that would be great news.
>>
> I don't subscribe to their business service, and I'm offered multiple file
> formats. Perhaps it's because I'm using their Costco card.


I use their Costco card and only get CSV and TXT (tab delimited).


--
Stephen M Butler, PMP, PSM
[hidden email]
[hidden email]
253-350-0166
-------------------------------------------
GnuPG Fingerprint:  8A25 9726 D439 758D D846 E5D4 282A 5477 0385 81D8

_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Greg Feneis
In reply to this post by Michael DeBusk-2
With no rational explanation, over the years I have tended to favor OFX
(perhaps because it's an open standard), and QFX (I think QuickBooks'
proprietary variation on OFX, because I used to use QuickBooks?) Anyway,
QFX and OFX is what I've been importing into GnuCash for the last several
years.

But I do enjoy a good challenge now and then, so I can also understand the
desire to write a file conversion script.

Kind regards, Greg Feneis
(Pixel 3)


On Mon, Jun 17, 2019, 10:13 Michael DeBusk <[hidden email]> wrote:

> On Mon, Jun 17, 2019 at 12:49 PM Greg Feneis <[hidden email]> wrote:
>
> > Is it too late to download the records of interest in QFX or OFX format?
> > GnuCash will import these formats as well.
> >
>
> It's not too late, but I never learned to appreciate QFX or OFX.
> --
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

GnuCash - User mailing list
In reply to this post by GnuCash - User mailing list
On Mon, Jun 17, 2019 at 1:46 PM Stephen M. Butler via gnucash-user <
[hidden email]> wrote:


> I use their Costco card and only get CSV and TXT (tab delimited).


Perhaps a call to customer service would be to your advantage.

--
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Michael DeBusk-2
In reply to this post by Greg Feneis
On Mon, Jun 17, 2019 at 1:55 PM Greg Feneis <[hidden email]> wrote:

> With no rational explanation, over the years I have tended to favor OFX
> (perhaps because it's an open standard), and QFX (I think QuickBooks'
> proprietary variation on OFX, because I used to use QuickBooks?) Anyway,
> QFX and OFX is what I've been importing into GnuCash for the last several
> years.
>

I've tried to figure out OFX and found it too complicated. I don't know how
to edit it.

--
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Greg Feneis
No need to edit OFX, or QFX files. GnuCash's import facility already knows
how to interpret the files.

Kind regards, Greg Feneis
(Pixel 3)


On Mon, Jun 17, 2019, 11:12 Michael DeBusk <[hidden email]> wrote:

> On Mon, Jun 17, 2019 at 1:55 PM Greg Feneis <[hidden email]> wrote:
>
>> With no rational explanation, over the years I have tended to favor OFX
>> (perhaps because it's an open standard), and QFX (I think QuickBooks'
>> proprietary variation on OFX, because I used to use QuickBooks?) Anyway,
>> QFX and OFX is what I've been importing into GnuCash for the last several
>> years.
>>
>
> I've tried to figure out OFX and found it too complicated. I don't know
> how to edit it.
>
> --
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
>
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Michael DeBusk-2
On Mon, Jun 17, 2019 at 4:24 PM Greg Feneis <[hidden email]> wrote:

> No need to edit OFX, or QFX files. GnuCash's import facility already knows
> how to interpret the files.
>
>>
That's the intent, I know. Still, the "payee" line is always a mess, and
splits are never part of what it "already knows," and sometimes I buy gas
at Royal Farms and sometimes I buy fried chicken and Gnucash can't know
which it was.

I'm cool with editing QIFs by hand if it means not having to edit
transactions in the Gnucash UI.

--
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

David Carlson-4
If you have been having issues with notes in OFX imports, there were a
couple of bugs in that area that were just fixed for release 3.6.  They
should already be in the Windows nightly builds.  Try OFX again when you
have the updates.

David Carlson

On Mon, Jun 17, 2019 at 4:21 PM Michael DeBusk <[hidden email]> wrote:

> On Mon, Jun 17, 2019 at 4:24 PM Greg Feneis <[hidden email]> wrote:
>
> > No need to edit OFX, or QFX files. GnuCash's import facility already
> knows
> > how to interpret the files.
> >
> >>
> That's the intent, I know. Still, the "payee" line is always a mess, and
> splits are never part of what it "already knows," and sometimes I buy gas
> at Royal Farms and sometimes I buy fried chicken and Gnucash can't know
> which it was.
>
> I'm cool with editing QIFs by hand if it means not having to edit
> transactions in the Gnucash UI.
>
> --
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>


--
David Carlson
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] CSV to QIF for Citibank

Michael DeBusk-2
On Mon, Jun 17, 2019 at 9:38 PM David Carlson <[hidden email]>
wrote:

> If you have been having issues with notes in OFX imports, there were a
> couple of bugs in that area that were just fixed for release 3.6.  They
> should already be in the Windows nightly builds.  Try OFX again when you
> have the updates.
>

Thanks. I'll give it a shot.

--
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.