{ "cells": [ { "cell_type": "markdown", "id": "61fa28f0", "metadata": {}, "source": [ "# Tax Calculations for financial year ending in 31st March 2024 #" ] }, { "cell_type": "code", "execution_count": 1, "id": "5f892d43-496b-4251-b2a5-7334ecc66f63", "metadata": {}, "outputs": [], "source": [ "#pip install quiffen\n", "#pip install openpyxl\n", "#pip install xlrd\n" ] }, { "cell_type": "code", "execution_count": 2, "id": "e5df445e", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "from quiffen import Qif" ] }, { "cell_type": "code", "execution_count": 3, "id": "72dc5a4c", "metadata": {}, "outputs": [], "source": [ "from datetime import date\n", "#from dotenv import load_dotenv\n", "#from airtable.airtable import Airtable" ] }, { "cell_type": "code", "execution_count": 4, "id": "d20c3d0a", "metadata": {}, "outputs": [], "source": [ "#load_dotenv()\n", "#AIRTABLE_APIKEY = os.environ.get(\"AIRTABLE_APIKEY\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "02d11889-90f0-4ec7-affc-be5627c0a3e6", "metadata": {}, "outputs": [], "source": [ "import warnings\n", "warnings.filterwarnings('ignore')" ] }, { "cell_type": "markdown", "id": "e67a880b", "metadata": {}, "source": [ "## Online Expenses ##" ] }, { "cell_type": "markdown", "id": "1af0c920", "metadata": {}, "source": [ "## Depreciation ##" ] }, { "cell_type": "markdown", "id": "4ab02a6d-f791-4a53-a145-78ba4b34ea24", "metadata": {}, "source": [ "### Compute Depreciation on HP ProBook Laptop used for work ###" ] }, { "cell_type": "code", "execution_count": 6, "id": "f1c99d63", "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Year ending 31 MarchOpening valueDepreciation rateDepreciation for yearClosing valueBusiness useClaim as tax deduction
020231898.0050% DV395.411502.590.95375.63
120241502.5950% DV751.29751.300.95713.72
22025751.3050% DV375.65375.650.95356.86
\n", "
" ], "text/plain": [ " Year ending 31 March Opening value Depreciation rate \\\n", "0 2023 1898.00 50% DV \n", "1 2024 1502.59 50% DV \n", "2 2025 751.30 50% DV \n", "\n", " Depreciation for year Closing value Business use Claim as tax deduction \n", "0 395.41 1502.59 0.95 375.63 \n", "1 751.29 751.30 0.95 713.72 \n", "2 375.65 375.65 0.95 356.86 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = pd.read_excel('HP-ProBook-650-G4-depreciation.xlsx', skiprows=12)\n", "df\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "48e57e4a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "713.72" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#iloc seems to be needed now to get a float out of it\n", "notebookDepreciation = float(df[df['Year ending 31 March']==2024]['Claim as tax deduction'].iloc[0])\n", "notebookDepreciation" ] }, { "cell_type": "markdown", "id": "71a2a57a", "metadata": {}, "source": [ "## ASB Expenses ##" ] }, { "cell_type": "code", "execution_count": 8, "id": "e10b8572", "metadata": {}, "outputs": [], "source": [ "qif = Qif.parse('asb-00.qif', day_first=False)\n", "acc = qif.accounts['Quiffen Default Account']" ] }, { "cell_type": "code", "execution_count": 9, "id": "5cc5270c", "metadata": {}, "outputs": [], "source": [ "asb00 = qif.to_dataframe()[['date', 'memo', 'payee', 'amount', 'check_number', 'line_number' ]]\n", "#asb00.head()" ] }, { "cell_type": "code", "execution_count": 10, "id": "8c04e177", "metadata": {}, "outputs": [], "source": [ "qif = Qif.parse('asb-50.qif', day_first=False)\n", "acc = qif.accounts['Quiffen Default Account']" ] }, { "cell_type": "code", "execution_count": 11, "id": "6a7a666c", "metadata": {}, "outputs": [], "source": [ "asb50 = qif.to_dataframe()[['date', 'memo', 'payee', 'amount', 'check_number', 'line_number' ]]\n", "#asb50.head()" ] }, { "cell_type": "markdown", "id": "8e93a3aa-1fc1-4ac1-9776-6d864f34a13a", "metadata": {}, "source": [ "No expenses claimed as a result of ASB accounts" ] }, { "cell_type": "markdown", "id": "d684823d-0e1f-45ce-bd2f-8702d1238e59", "metadata": {}, "source": [ "### Co-Operative Bank Accounts ###" ] }, { "cell_type": "code", "execution_count": 12, "id": "6efecffd", "metadata": {}, "outputs": [], "source": [ "qif = Qif.parse('02-1248-0836615-001_01Apr2023_31Mar2024.Qif', day_first=False)\n", "acc = qif.accounts['Quiffen Default Account']" ] }, { "cell_type": "code", "execution_count": 13, "id": "bb06fd46", "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datememopayeeamountcheck_numberline_number
02024-03-31EFTPOS BEACHLENDS SUPERETTENone-19.00None1
12024-03-27EFTPOS WOOLWORTHS NZ 9177None-64.22None7
22024-03-26DC- WEBQUEST LIMITED INV-1803-2None320.00None13
32024-03-26TRANSACTION FEES - 26/02 TO 25/03None-3.50None19
42024-03-23EFTPOS WOOLWORTHS NZ 9177None-22.19None25
\n", "
" ], "text/plain": [ " date memo payee amount check_number \\\n", "0 2024-03-31 EFTPOS BEACHLENDS SUPERETTE None -19.00 None \n", "1 2024-03-27 EFTPOS WOOLWORTHS NZ 9177 None -64.22 None \n", "2 2024-03-26 DC- WEBQUEST LIMITED INV-1803-2 None 320.00 None \n", "3 2024-03-26 TRANSACTION FEES - 26/02 TO 25/03 None -3.50 None \n", "4 2024-03-23 EFTPOS WOOLWORTHS NZ 9177 None -22.19 None \n", "\n", " line_number \n", "0 1 \n", "1 7 \n", "2 13 \n", "3 19 \n", "4 25 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "coop001 = qif.to_dataframe()[['date', 'memo', 'payee', 'amount', 'check_number', 'line_number' ]]\n", "coop001.head()" ] }, { "cell_type": "code", "execution_count": 14, "id": "60972c9f-494a-4de3-81a0-e3dd18a1a38c", "metadata": {}, "outputs": [], "source": [ "#coop001['memo'].unique()" ] }, { "cell_type": "markdown", "id": "0f58e37e", "metadata": {}, "source": [ "## Co-op Account Income & Expenses ##" ] }, { "cell_type": "code", "execution_count": 15, "id": "c67a7928-30ca-4a81-9cac-01f382b89d43", "metadata": {}, "outputs": [], "source": [ "qif = Qif.parse('02-1248-0836615-002_01Apr2023_31Mar2024.Qif', day_first=False)\n", "acc = qif.accounts['Quiffen Default Account']" ] }, { "cell_type": "code", "execution_count": 16, "id": "6e1bacf2", "metadata": { "scrolled": true }, "outputs": [], "source": [ "coop002 = qif.to_dataframe()[['date', 'memo', 'payee', 'amount', 'check_number', 'line_number' ]]\n", "# coop002.head()" ] }, { "cell_type": "code", "execution_count": 17, "id": "3ba888ca-77db-425b-bf3c-46eafbc196c4", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "22.8" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "adblock = np.abs(coop002[coop002['memo'].str.contains(\"ADBLO\")]['amount'].sum())\n", "adblock" ] }, { "cell_type": "code", "execution_count": 18, "id": "a3de2b35-98f8-4120-b9d6-27086e0cb44b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datememopayeeamountcheck_numberline_number
1342023-05-30POS PAYPAL *BOSEPTYLTDNone-499.95None805
\n", "
" ], "text/plain": [ " date memo payee amount check_number line_number\n", "134 2023-05-30 POS PAYPAL *BOSEPTYLTD None -499.95 None 805" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Find the amount paid for the Bose Noise Cancelling headphones. Essential to maintain a \n", "# quiet office environment free from distractions.\n", "\n", "bose_headphones = np.abs(coop002[coop002['memo'].str.contains('Bose', case=False)]['amount'].sum())\n", "coop002[coop002['memo'].str.contains('Bose', case=False)]" ] }, { "cell_type": "code", "execution_count": 19, "id": "0c1f8903-7363-4707-a754-b224467f9aa9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "499.95" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bose_headphones" ] }, { "cell_type": "code", "execution_count": 20, "id": "24718ab5-78d8-461a-8cc7-11391614a97e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "25.0" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Compute the amount spent on the Talk360 internet to phone service ( negative indicates expense )\n", "talk360 = np.abs(coop001[coop001.memo.str.contains('talk360')]['amount'].sum())\n", "talk360" ] }, { "cell_type": "code", "execution_count": 21, "id": "37fb59e2", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "7040" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Sole source of Income\n", "webquest = coop001[coop001.memo.str.contains('webquest', case=False)].count()['date']\n", "total_webquest = webquest *320\n", "total_webquest" ] }, { "cell_type": "code", "execution_count": 22, "id": "a7d5754f", "metadata": { "scrolled": true }, "outputs": [], "source": [ "#coop002.memo.unique()" ] }, { "cell_type": "code", "execution_count": 23, "id": "08cc679d", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "286.0" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "spark_mobile = np.abs(coop002[coop002.memo.str.contains('spark', case=False)]['amount'].sum())\n", "spark_mobile\n" ] }, { "cell_type": "code", "execution_count": 24, "id": "ad7b4ea4", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "239.47" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ramnode = np.abs(coop002[coop002.memo.str.contains('ramnode', case=False)]['amount'].sum())\n", "ramnode\n" ] }, { "cell_type": "code", "execution_count": 25, "id": "61a030e4-af37-492c-9c89-921f4c72283e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "499.95" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bose_headphones" ] }, { "cell_type": "markdown", "id": "bbb07919", "metadata": {}, "source": [ "## Calculate 20% of board as office space ##\n", "\n", "I moved back to Whanganui 24th April 2022" ] }, { "cell_type": "code", "execution_count": 26, "id": "b457b46b", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "48" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "num_board_pay_periods = int((date(2023,3,31) - date(2022, 4, 24)).days / 7)\n", "num_board_pay_periods" ] }, { "cell_type": "code", "execution_count": 27, "id": "77ffcf78", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "11520" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "total_board_paid = num_board_pay_periods * 240\n", "total_board_paid" ] }, { "cell_type": "markdown", "id": "b3a4515e", "metadata": {}, "source": [ "## Final Calculation ##" ] }, { "cell_type": "markdown", "id": "cd989e1d-b0b3-424a-8af2-8b5135e8108b", "metadata": {}, "source": [ "### Total Income ###" ] }, { "cell_type": "code", "execution_count": 28, "id": "0b1c4a4a-f5c0-4dec-89ff-75d7d2a8ca8f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "7040" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "total_income = total_webquest\n", "total_income" ] }, { "cell_type": "markdown", "id": "67772679-aa9a-4696-bf51-2165fecbf190", "metadata": {}, "source": [ "### Total Expenses ###" ] }, { "cell_type": "code", "execution_count": 29, "id": "fe4b64eb", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Spark Mobile 286.00\n", "Ramnode VPS 239.47\n", "HP ProBook 650 G4 Depreciation 713.72\n", "Bose Headphones 499.95\n", "Talk360 internet service 25.00\n", "Ad Blocker 22.80\n", "20% of board as office space 2304.00\n", "dtype: float64" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dct = {\"Spark Mobile\":spark_mobile, \n", " 'Ramnode VPS': ramnode, \"HP ProBook 650 G4 Depreciation\":notebookDepreciation, \n", " 'Bose Headphones': bose_headphones,\n", " 'Talk360 internet service': talk360,\n", " 'Ad Blocker': adblock,\n", " '20% of board as office space':total_board_paid*.20}\n", "df = pd.Series(dct)\n", "df" ] }, { "cell_type": "code", "execution_count": 30, "id": "b754a956", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4090.94" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "total_expenses = df.sum()\n", "total_expenses" ] }, { "cell_type": "markdown", "id": "79899b85-cf1a-43d3-b0a8-407e088927ca", "metadata": {}, "source": [ "### Net Income ###" ] }, { "cell_type": "code", "execution_count": 31, "id": "b6e42196-2bea-4ffd-a525-340a7ec85411", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2949.06" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "total_income - total_expenses" ] }, { "cell_type": "code", "execution_count": null, "id": "96b0f3b2-4fd2-4a53-af5a-cb6264f275ba", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "772bff2c-efe9-416e-bd33-c68f7e58af95", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.10" } }, "nbformat": 4, "nbformat_minor": 5 }