Example MMEX Transfer account data / balances to Excel.

General discussion on all other topics under the sun.
Post Reply
User avatar
Renato
MVP MMEX User
Posts: 140
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich
Contact:

Example MMEX Transfer account data / balances to Excel.

Post by Renato »

My template was: https://docplayer.org/28463944-Silverju ... inden.html

First the ODBC drivers for Windows / Excel must be installed.

http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe
http://www.ch-werner.de/sqliteodbc/sqliteodbc_w64.exe

Afterwards these drivers can be configured.
Starting the APP ODBC Data Sources (64bit)
Add the SQLite3ODBC driver under user DSN
In the configuration of the SQLite3ODBC driver you define a name (*) and enter the location of the .mmb database.

Now the access can be set up in EXCEL (example Excel 2019).
In an empty worksheet, select the data source name (DSN) (*) under Data / Retrieve data / from other sources / from ODBC

in the advanced options you enter the following SQL statements:

select a.ACCOUNTNAME, a. Heldat, a. Status
, a.INITIALBAL + total(t.TRANSAMOUNT) as Balance
from
(select ACCOUNTID, TRANSDATE, STATUS,
(case when TRANSCODE='Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE='Transfer') as t
inner join ACCOUNTLIST_V1 as a on a.ACCOUNTID=t.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c on a.CURRENCYID=c.CURRENCYID
where a.Status='Open'
and t.STATUS<>'V'
and a.accountname like 'CS%' or a.accountname like 'SG%' or a.accountname like '.%' or a.accountname like 'Spar%' or a.accountname like 'Post%
group by a.ACCOUNTID
order by HELDAT, ACCOUNTNAME;

you must replace the selection of accounts (e.g. CS%) with the desired accounts.

Of course I did not describe every step in detail, I assume a little bit of knowledge.
regards Renato use MMEX since 2009

Post Reply