Project xlmacros

Location=C:/Users/s/Documents/py/xlmacros

aaa summary

Description

Contains basic modules used for all python projects

_creds.py = credentials ipstartup = ipython setup including common imports logconfig = logging configuration spycon = converts py=>ipynb and back

xlinvest

Description

This contains python code to be called from excel via xlwings.

Excel functions (to put in cells):

£=get_quote(code)
$=get_dollarpound()

Excel macros (to run directly or attach to buttons):

getportfolio()
gettransactions()

Dependencies:

xlwings, logconfig (optional), pydal, invest, Quandl

Instances

db = <class 'pydal.base.DAL'>

log = <class 'logging.RootLogger'>

Functions

get_dollarpound()

from quandl cached in local db.

get_portfolio()

downloads investments from hargreaves lansdown to excel

get_quote(code)

from yahoo cached in local db

get_transactions()

downloads capital transactions from hargreaves lansdown to excel

isbuy(trans)

issell(trans)

Imported modules

xlwings, logconfig, invest.hl

xlmacros

Description

Enable python code to be called as Excel functions or macros via xlwings This script iterates through all modules in the folder.

Usage:

Put the functions in .py files in this folder
Install python dependencies as required
In this file set UDF_PATH = /path/to/thisfile
In xlmacros.xlsm
    set UDF_PATH = /path/to/thisfile
    run macro ImportPythonUDFs to link new scripts to excel
    save as excel macro enabled template xlmacros.xltm

To create new workbook File/New/from template/xlmacros.xltm

Instances

UDF_PATH = <class 'str'>

clientid = <class 'str'>

clients = <class 'list'>

db = <class 'pydal.base.DAL'>

gcreds = <class 'dict'>

Functions

main()

'from module import *' for each module in folder

Imported modules

invest.hl, googlemaps, xlwings, logconfig

xlmaps

Description

This contains python code to be called from excel via xlwings.

Excel functions (to put in cells):

km=distance(orig, dest)
mins=driving_time(orig, dest)
mins=walking_time(orig, dest)

Dependencies:

xlwings, logconfig (optional), googlemaps

add to _creds.py [2500/day requests/key]
    googlemaps=dict(browserkeys=<list of googlemaps keys>)

Instances

clientid = <class 'str'>

clients = <class 'list'>

gcreds = <class 'dict'>

Functions

distance(orig, dest)

driving_time(orig, dest)

walking_time(orig, dest)

Imported modules

googlemaps