In [1]:

    

import shutil, os
import pandas as pd
from gspread_dataframe import set_with_dataframe

project_dir = '/Users/max/budgeter/'
template_file = './templates/simple_payment_list.xlsx'
temp_name = './entry_temp.xlsx'

def is_number(s) :
    try :
        float(s)
        return True
    except ValueError :
        return False


    

In [2]:

    

os.chdir(project_dir)

shutil.copy(template_file, temp_name)

os.system('open ' + temp_name)
input('Make any input to continue.')


    

    




Make any input to continue.






    
Out[2]:





''

In [3]:

    

data_is_final = False

while not data_is_final :
    read_data = pd.read_excel(temp_name, skiprows=1)
    
    ## Here one could do a replace with a dict if the transaction type was abbreviated

    print('The read data is:')
    display(read_data)
    print('')

    request = input('If this is not the intended data, enter any number:')

    if not is_number(request) :
        print('OK.')
        data_is_final = True
    else : 
        os.system('open ' + temp_name)
        input('Make any input to continue.')


    

    




The read data is:






    








  

    

      

      
date
      
description
      
amount
      
budget_type
      
transaction_type
      
money_pot
    
  
  

    

      
0
      
2017-08-25
      
Eis
      
-1.2
      
A
      
Barzahlung
      
BM
    
    

      
1
      
2017-08-25
      
Suppe
      
-0.6
      
AM
      
Kartenzahlung
      
CB
    
  








    




If this is not the intended data, enter any number:
OK.

In [4]:

    

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def connect_to_sheets(spreadsheet='GemeinsameBilanzierung_16_17', sheetname='') :
    
    if type(spreadsheet) is str : 
        scope = ['https://spreadsheets.google.com/feeds']
        creds = ServiceAccountCredentials.from_json_keyfile_name('client-secret.json', scope)
        client = gspread.authorize(creds)
        spreadsheet = client.open(spreadsheet)

    # Find a workbook by name and open the first sheet
    # Make sure you use the right name here.
    if sheetname == '' :
        return spreadsheet
    else :
        worksheet = spreadsheet.worksheet(sheetname)
        return worksheet
    
workbook = connect_to_sheets()
sheet = connect_to_sheets(workbook, 'August')


    

In [33]:

    

new_budget_info = read_data[['budget_type', 'description', 'date', 'amount']].copy()
new_budget_info.loc[:,'date'] = new_budget_info['date'].dt.strftime('%d/%m/%y')
new_budget_info.loc[:,'amount'] = new_budget_info['amount'].map('{:,.2f} €'.format)
new_budget_info.loc[:,'amount'] = new_budget_info['amount'].str.replace('.',',')
display(new_budget_info)


    

    








  

    

      

      
budget_type
      
description
      
date
      
amount
    
  
  

    

      
0
      
A
      
Eis
      
25/08/17
      
-1,20 €
    
    

      
1
      
AM
      
Suppe
      
25/08/17
      
-0,60 €
    
  

In [37]:

    

the_col_A = 1
starting_row = 5
new_row = sheet.col_values(the_col_A).index('', starting_row)

set_with_dataframe(sheet, right_side, row=new_row, col=the_col_A, include_column_header=False)


    

In [35]:

    

new_max_bargeld_info = read_data.loc[read_data['money_pot'] == 'BM']
new_max_bargeld_info = new_max_bargeld_info[['description', 'date', 'amount']].copy()

new_max_bargeld_info['date'] = new_max_bargeld_info['date'].dt.strftime('%d/%m/%y')
new_max_bargeld_info.loc[:,'amount'] = new_max_bargeld_info['amount'].map('{:,.2f} €'.format)
new_max_bargeld_info.loc[:,'amount'] = new_max_bargeld_info['amount'].str.replace('.',',')

display(new_max_bargeld_info)


    

    








  

    

      

      
description
      
date
      
amount
    
  
  

    

      
0
      
Eis
      
25/08/17
      
-1,20 €
    
  

In [38]:

    

the_col_H = 8
starting_row = 5
new_row = sheet.col_values(the_col_H).index('', starting_row)

set_with_dataframe(sheet, max_side, row=new_row, col=the_col_H, include_column_header=False)


    

In [ ]:

    

starting_col = {'BM' : 8,    # col H
                'BP' : 11,   # col K
                'GK' : 14 }  # col N
starting_row = 5

def update_big_pots(sheet, full_info, money_pot='GK', 
                    cols=['description', 'date', 'amount'],
                    date_col='date', amount_col='amount', money_pot_col='money_pot') :
    if date_col not in cols or amount_col not in cols :
        raise ValueError
    new_info = full_info.loc[read_data[money_pot_col] == money_pot]
    new_info = new_info[cols].copy()
    
    new_info[date_col] = new_info[date_col].dt.strftime('%d/%m/%y')
    new_col = new_info[amount_col].map('{:,.2f} €'.format)
    new_info[amount_col] = new_col.str.replace('.',',')
    
    display(new_info)
    
    first_empty_row = sheet.col_values(starting_col[money_pot]).index('', starting_row)
    set_with_dataframe(sheet, new_info, row=first_empty_row, col=starting_col[money_pot], 
                       include_column_header=False)