Dear expert,
Set up:
A sqlite3 database (DB) is updated each morning at 5am via Python-coded datascraping from web and other API sources. In order to perform calculations I use Microsoft Excel and then import the data as KPIs back into the DB. The source files are financial statements of public companies (Income statement, balance sheet and the cash flow statement in annual and quarterly version - in total 6 csv files per company). I have 2 excel files , called CPA and XXX_kpi (which is in the specific format required for my sqlite3 DB).
1. My main excel workbook (called 'CPA') (which calculates the KPIs) needs to
a. be opened in the background or (if easier) each time when I run a macro - if the computer/excel is blocked during this time no problem - I need to do this only once each month
b. Create a VB script which will feed data into Sheets: MStar IS, MStar BS, MStar CF, MStar Q_IS, MStar Q_BS, MStar Q_CF - always in cells CD$12:$AQ$238 (GREEN marked) for all 6 csv sheets
c. Keep the data/year which are outdated, adjust green arean, e.g. in 2020, extend 1 column
D.Compile data from respective sheet and save with the name of a specific cell in this workbook (e.g.NYSE.DE) in a specific folder and close
2. In a dedicated folder I have an import file for each company (e.g. NYSE.DE_kpi) which is currently pulling the KPIs via powerquery from 2 sheets in the CPA file (Export). This workbook needs to be
a. be opened in the background or (if easier) each time when I run a macro - if the computer/excel is blocked during this time no problem - I need to do this only once each month) - you may integrate this macro into the first one (if possible?)
b. Save and close as a normal workbook
3. (Optional) There are 2 power queries in the sheet which are linked to the sqlite3 database via odbc - the 2 data connections are called 'Prices' and 'Dividends' - it would be fantastic if you could write a code how to refer successfully refer to a cell within the query SQL (e.g. DE.US is cell Cockpit!M12)
I will share the files & file structure of my local computer with shortlisted candidates.
Please ask any question and quote a fixed price.
Thanks a lot - looking forward working with you.
About the recuiterMember since Sep 16, 2017 Jenny Nieves
from Alberta, Canada