CSV import via API?

Hi,

Since my banks are not supported, I'm stuck on CSV imports. I wrote a tool that converts my bank's CSVs to a format that can be imported into YNAB and I thought I could simplify the process by using the YNAB API to upload them.

Is it possible to upload a CSV over the API in a way that works just like in the web app i.e. it will skip duplicate transactions and mark them for review so I can then easily identify the new transactions manually?

If not, how does the existing endpoint behave when you try to post duplicate transactions? Could you add the behavior for duplicate transactions to the documentation at https://api.youneedabudget.com/v1#/Transactions/createTransaction ?

If using createTransaction creates duplicates, which approach would you recommend to import the CSV? Would I have to re-implement deduplication client-side or is there a simpler way?

Thanks!

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi there Blue Wizard !

    This is a great question! The transaction's import_id field is what determines whether a transaction is a duplicate when importing. In the POST Transaction Endpoint documentation you linked, in the "data" section, click the "Model" tab for more information about import_id and how it's constructed. That should give you some ideas on how to use it to avoid duplicates and match up transactions if you're looking to do that.

    I hope that helps!

    Like
      • Blue Wizard
      • Blue_Wizard.14
      • 4 mths ago
      • Reported - view

      Sarah (YNAB Support) Excellent, thanks a lot, that works perfectly!

      Slight note: In the modell, it says that you should spell "cleared" in lower case, while the example code spells it in upper case: https://github.com/ynab/ynab-sdk-ruby/blob/master/examples/create-multiple-transactions.rb#L19
      I used the example code spelling, which works. Maybe that should be unified?

      Like
    • Blue Wizard Thanks for the heads up on that capitalization discrepancy! It should work either way, but we appreciate you letting us know it tripped you up!

      Like
  • In case anybody wants to upload a CSV over the command line, here's a simple ruby script to do so. You call it like this:

    ynab_upload_csv accessToken budgetId accountId myBanksCSVexport.csv

    #!/usr/bin/ruby
    
    # Imports a YNAB CSV into a YNAB budget's account
    
    require 'bigdecimal'
    require 'csv'
    require 'pp'
    require 'ynab'
    
    # How to call this program:
    access_token, budget_id, account_id, csv_path = ARGV
    
    ynab = YNAB::API.new(access_token)
    
    def generate_import_id_for(transaction)
      "YNAB:#{transaction[:amount]}:#{transaction[:date].to_s}:1"
    end
    
    # Load transactions from given CSV file
    ynab_csv     = CSV.read(csv_path)
    rows         = ynab_csv.drop(1)   # drop the column names
    transactions = rows.map do |row|
      usa_date, payee, memo, stringamount = row
      transaction = {
        account_id: account_id,
        amount: ((BigDecimal(stringamount) * 1000).to_i), # milliunits format
        cleared: "Cleared",
        date: Date.strptime(usa_date, "%m/%d/%y"), # random USA order: 12/31/93
        memo: memo,
        payee_name: payee
      }
      transaction[:import_id] = generate_import_id_for(transaction)
      transaction
    end
    
    # pp transactions
    begin
      pp ynab.transactions.create_transaction( budget_id, {transactions: transactions})
    rescue YNAB::ApiError => e
      puts "ERROR: id=#{e.id}; name=#{e.name}; detail: #{e.detail}"
    end
    
    Like 1
  • Hi Blue Wizard I'm planning to write some automation around this stuff too. Do you happen to have a github where I could see your ynab stuff, for reference? Thanks.

    (Also, FYI, your `generate_import_id_for` will fail for two transactions of equal amounts on the same day. E.g. buy a coffee in the morning, buy a coffee in the afternoon. )

    Like
    • Hey Sea Green Sun,

      While I do have some more stuff (e.g. americanizing CSVs to make them YNAB-compatible), none of that is on Github yet, because it's currently a bit messy (It's a mix of bash, Clojure and Ruby) and it's somewhat low priority right now. When I get to it, I'll let you know and post it here and/or on the reddit.

      Thanks for pointing out the bug regarding the duplicate IDs. The second one should end with ":2" instead of ":1".


      There is also another bug: It doesn't work for future transaction, since they would need to be added over a different API end point. So far, I could ignore that problem, since my CSVs rarely contain future transactions anyways.

      Like
Like1 Follow
  • 1 Likes|like
  • 2 wk agoLast active
  • 6Replies
  • 139Views
  • 5 Following