QuickBooks_API.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install intuit-oauth\n",
    "#!pip install selenium\n",
    "\n",
    "# Selenium requires a driver to interface with the chosen browser. \n",
    "# Chrome driver https://sites.google.com/a/chromium.org/chromedriver/downloads"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "https://developer.intuit.com/app/developer/qbpayments/docs/develop/authentication-and-authorization\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#setup client_id (application id)\n",
    "client_id = ''\n",
    "\n",
    "#setup client_secret (application secret)\n",
    "client_secret = '' \n",
    "\n",
    "#setup redirect_url (application redirect url)\n",
    "redirect_uri = 'https://appcenter.intuit.com/'\n",
    "\n",
    "#setup environment sandbox/production\n",
    "environment = 'sandbox' #'production'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from intuitlib.client import AuthClient\n",
    "from intuitlib.enums import Scopes\n",
    "from selenium import webdriver\n",
    "import time\n",
    "import datetime\n",
    "import keyring\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import requests\n",
    "\n",
    "\n",
    "assert client_id, 'client_id is empty'\n",
    "assert client_secret, 'client_secret is empty'\n",
    "assert redirect_uri, 'redirect_url is empty'\n",
    "\n",
    "if environment == 'sandbox':\n",
    "    baseURL = 'https://sandbox-quickbooks.api.intuit.com'\n",
    "else:\n",
    "    baseURL = 'https://quickbooks.api.intuit.com'\n",
    "\n",
    "# Prepare scopes\n",
    "scopes = [Scopes.ACCOUNTING]\n",
    "\n",
    "auth_client = AuthClient(\n",
    "        client_id,\n",
    "        client_secret,\n",
    "        redirect_uri,\n",
    "        environment,\n",
    "    )\n",
    "\n",
    "#refresh token max age (days)\n",
    "token_max_age = 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def FirstAuth():\n",
    "    # Get authorization URL\n",
    "    auth_url = auth_client.get_authorization_url(scopes)\n",
    "\n",
    "    # Chrome webdriver      \n",
    "    chrome_options = webdriver.ChromeOptions()\n",
    "    chrome_options.add_argument(\"--incognito\")\n",
    "    driver = webdriver.Chrome(options=chrome_options)\n",
    "    driver.get(auth_url)\n",
    "    auth_res_url = auth_url\n",
    "    #waiting while there will be \"code\" in the browser's URL\n",
    "    while auth_res_url.find('code=') == -1:\n",
    "        time.sleep(1) # 1 second sleep\n",
    "        auth_res_url = driver.current_url        \n",
    "\n",
    "    start_number = auth_res_url.find('code=') + len('code=')\n",
    "    end_number = auth_res_url.find('&state=')\n",
    "    auth_code = auth_res_url[start_number:end_number]\n",
    "\n",
    "    start_number = auth_res_url.find('realmId=') + len('realmId=')\n",
    "    realm_id = auth_res_url[start_number:]\n",
    "    \n",
    "    #print(f'auth_code = {auth_code}')\n",
    "    #print(f'realm_id = {realm_id}')\n",
    "    \n",
    "    # Get OAuth2 Bearer token\n",
    "    auth_client.get_bearer_token(auth_code, realm_id=realm_id)\n",
    "    \n",
    "    #print(f'access_token = {auth_client.access_token}')\n",
    "    #print(f'refresh_token = {auth_client.refresh_token}')\n",
    "        \n",
    "    # Return tokens\n",
    "    return realm_id\n",
    "\n",
    "# Data API\n",
    "def GetData(api_url, data_type):  \n",
    "      \n",
    "    last_page = False\n",
    "    ret = dict()\n",
    "    while not last_page: \n",
    "        response = requests.get(api_url, \n",
    "                               headers = {\n",
    "                                   'Authorization': 'Bearer ' + auth_client.access_token,\n",
    "                                   'Accept': 'application/json'\n",
    "                               })\n",
    "       \n",
    "        #print(response)\n",
    "        \n",
    "        if response.status_code!=200:\n",
    "            print(f'Bad server response for /{data_type}/ : {response}')\n",
    "            if response.status_code not in (401, 403, 404, 502):\n",
    "                print(f'Retrieved JSON:\\n{response.json()}')\n",
    "            return ret;    \n",
    "        else:\n",
    "            json_response = response.json()\n",
    "           \n",
    "            data_response = json_response[data_type]\n",
    "            \n",
    "            ret = data_response \n",
    "            \n",
    "            last_page=True\n",
    "        \n",
    "    return ret"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "refresh_token_age = -1\n",
    "token_date = keyring.get_password('qb_refresh_token_date', getpass.getuser())\n",
    "\n",
    "if token_date is not None:\n",
    "    token_date = datetime.datetime.fromtimestamp(int(float(token_date)))\n",
    "    #print(f'refresh_token_date = {token_date}')\n",
    "    current_date = datetime.datetime.now() \n",
    "    refresh_token_age = (current_date - token_date).days\n",
    "    #print(f'refresh_token_age = {refresh_token_age}')\n",
    "    \n",
    "if (refresh_token_age < 0) | (refresh_token_age > token_max_age):    \n",
    "    realm_id = FirstAuth()\n",
    "    keyring.set_password('qb_realm_id', getpass.getuser(), realm_id)    \n",
    "else:\n",
    "    auth_client.refresh_token = keyring.get_password(\"qb_refresh_token\", getpass.getuser())\n",
    "    realm_id = keyring.get_password(\"qb_realm_id\", getpass.getuser())\n",
    "\n",
    "#refresh tokens\n",
    "auth_client.refresh()\n",
    "#save tokens\n",
    "keyring.set_password('qb_refresh_token', getpass.getuser(), auth_client.refresh_token)\n",
    "keyring.set_password('qb_refresh_token_date', getpass.getuser(), str(datetime.datetime.now().timestamp()))\n",
    "\n",
    "#print(f'access_token = {auth_client.access_token}')\n",
    "#print(f'refresh_token = {auth_client.refresh_token}')\n",
    "\n",
    "\n",
    "api_uri = baseURL+'/v3/company/'+realm_id;\n",
    "accounts_select = 'select * from Account where Metadata.CreateTime > \\'2014-12-31\\''\n",
    "accounts_URL = api_uri+'/query?query='+accounts_select \n",
    "client = '1';\n",
    "ProfitAndLoss_URL = api_uri+'/reports/ProfitAndLoss?name='+client\n",
    "JournalReport_URL = api_uri+'/reports/JournalReport?name='+client\n",
    "\n",
    "\n",
    "df_accounts = pd.DataFrame(GetData(accounts_URL, 'QueryResponse'))\n",
    "df_profit_loss = pd.DataFrame(GetData(ProfitAndLoss_URL, 'Header'))\n",
    "df_journal_report = pd.DataFrame(GetData(JournalReport_URL, 'Header'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>startPosition</th>\n",
       "      <th>maxResults</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'Name': 'Accounts Payable (A/P)', 'SubAccount...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'Name': 'Accounts Receivable (A/R)', 'SubAcco...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'Name': 'Advertising', 'SubAccount': False, '...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'Name': 'Arizona Dept. of Revenue Payable', '...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'Name': 'Automobile', 'SubAccount': False, 'F...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>{'Name': 'Undeposited Funds', 'SubAccount': Fa...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>{'Name': 'Utilities', 'SubAccount': False, 'Fu...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>{'Name': 'Gas and Electric', 'SubAccount': Tru...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>{'Name': 'Telephone', 'SubAccount': True, 'Par...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>{'Name': 'Visa', 'SubAccount': False, 'FullyQu...</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>89 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Account  startPosition  \\\n",
       "0   {'Name': 'Accounts Payable (A/P)', 'SubAccount...              1   \n",
       "1   {'Name': 'Accounts Receivable (A/R)', 'SubAcco...              1   \n",
       "2   {'Name': 'Advertising', 'SubAccount': False, '...              1   \n",
       "3   {'Name': 'Arizona Dept. of Revenue Payable', '...              1   \n",
       "4   {'Name': 'Automobile', 'SubAccount': False, 'F...              1   \n",
       "..                                                ...            ...   \n",
       "84  {'Name': 'Undeposited Funds', 'SubAccount': Fa...              1   \n",
       "85  {'Name': 'Utilities', 'SubAccount': False, 'Fu...              1   \n",
       "86  {'Name': 'Gas and Electric', 'SubAccount': Tru...              1   \n",
       "87  {'Name': 'Telephone', 'SubAccount': True, 'Par...              1   \n",
       "88  {'Name': 'Visa', 'SubAccount': False, 'FullyQu...              1   \n",
       "\n",
       "    maxResults  \n",
       "0           89  \n",
       "1           89  \n",
       "2           89  \n",
       "3           89  \n",
       "4           89  \n",
       "..         ...  \n",
       "84          89  \n",
       "85          89  \n",
       "86          89  \n",
       "87          89  \n",
       "88          89  \n",
       "\n",
       "[89 rows x 3 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>ReportName</th>\n",
       "      <th>DateMacro</th>\n",
       "      <th>ReportBasis</th>\n",
       "      <th>StartPeriod</th>\n",
       "      <th>EndPeriod</th>\n",
       "      <th>SummarizeColumnsBy</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Option</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-17T20:40:53-07:00</td>\n",
       "      <td>ProfitAndLoss</td>\n",
       "      <td>this calendar year-to-date</td>\n",
       "      <td>Accrual</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-10-17</td>\n",
       "      <td>Total</td>\n",
       "      <td>USD</td>\n",
       "      <td>{'Name': 'AccountingStandard', 'Value': 'GAAP'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-17T20:40:53-07:00</td>\n",
       "      <td>ProfitAndLoss</td>\n",
       "      <td>this calendar year-to-date</td>\n",
       "      <td>Accrual</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-10-17</td>\n",
       "      <td>Total</td>\n",
       "      <td>USD</td>\n",
       "      <td>{'Name': 'NoReportData', 'Value': 'false'}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Time     ReportName                   DateMacro  \\\n",
       "0  2020-10-17T20:40:53-07:00  ProfitAndLoss  this calendar year-to-date   \n",
       "1  2020-10-17T20:40:53-07:00  ProfitAndLoss  this calendar year-to-date   \n",
       "\n",
       "  ReportBasis StartPeriod   EndPeriod SummarizeColumnsBy Currency  \\\n",
       "0     Accrual  2020-01-01  2020-10-17              Total      USD   \n",
       "1     Accrual  2020-01-01  2020-10-17              Total      USD   \n",
       "\n",
       "                                            Option  \n",
       "0  {'Name': 'AccountingStandard', 'Value': 'GAAP'}  \n",
       "1       {'Name': 'NoReportData', 'Value': 'false'}  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_profit_loss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>ReportName</th>\n",
       "      <th>DateMacro</th>\n",
       "      <th>StartPeriod</th>\n",
       "      <th>EndPeriod</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Option</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-17T20:40:53-07:00</td>\n",
       "      <td>JournalReport</td>\n",
       "      <td>this month-to-date</td>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>2020-10-17</td>\n",
       "      <td>USD</td>\n",
       "      <td>{'Name': 'NoReportData', 'Value': 'true'}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Time     ReportName           DateMacro StartPeriod  \\\n",
       "0  2020-10-17T20:40:53-07:00  JournalReport  this month-to-date  2020-10-01   \n",
       "\n",
       "    EndPeriod Currency                                     Option  \n",
       "0  2020-10-17      USD  {'Name': 'NoReportData', 'Value': 'true'}  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_journal_report"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### In case if needed to clear saved passwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#keyring.delete_password(\"qb_refresh_token\", getpass.getuser())\n",
    "#keyring.delete_password(\"qb_refresh_token_date\", getpass.getuser())\n",
    "#keyring.delete_password(\"qb_realm_id\", getpass.getuser())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Xero API",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}