BI tool integration

[koo5]

amounts transacted by transaction type.. possible export to html or something?

[koo5]

# dependencies:
#
# Python:
#  pandas == 0.25.0
#  bokeh == 1.3.4
#  selenium == 3.141 (only needed for exporting to PNG)
#
# JS:
#  phantomjs == 2.1.1 (only needed for exporting to PNG)

from math import pi

import pandas as pd
import json
from collections import defaultdict

from bokeh.io import output_notebook, show, export_png
from bokeh.palettes import Category20c
from bokeh.plotting import figure
from bokeh.transform import cumsum

output_notebook()

with open("general_ledger2.json", "r") as JSON:
    json_dict = json.load(JSON)

GL_defaultdict = defaultdict(dict)
for source in json_dict:
    GL_defaultdict[source["source"]["account"]][source["source"]["verb"]] = source["source"]["report_currency_transacted_amount"]

GL_dict = dict(GL_defaultdict)

gl_data = pd.Series(GL_dict["WellsFargo"]).reset_index(name='value').rename(columns={'index':'verb'})
gl_data['angle'] = gl_data['value']/gl_data['value'].sum() * 2*pi
gl_data['color'] = Category20c[len(GL_dict["WellsFargo"])]

plot = figure(plot_height=350, title="Wells Fargo", toolbar_location=None,
        tools="hover", tooltips="@verb: @value")

plot.wedge(x=0, y=1, radius=0.4,
        start_angle=cumsum('angle', include_zero=True), end_angle=cumsum('angle'),
        line_color="white", fill_color='color', legend='verb', source=gl_data)

plot.axis.visible = False
plot.xgrid.grid_line_color = None
plot.ygrid.grid_line_color = None
plot.outline_line_color = None
plot.legend.border_line_width = 2
plot.legend.border_line_color = "black"

show(plot)
export_png(plot, filename="WellsFargoPieChartStatic.png")

• https://pastebin.com/raw/BAMjeudP https://imgur.com/a/mj1Y0BJ

[sto0pkid]

Code from previous pastebin is intended to be run inside a Jupyter notebook. The chart is interactive if displayed in Jupyter

On Fri, Aug 30, 2019 at 12:52 PM koo5 notifications@github.com wrote:

https://imgur.com/a/mj1Y0BJ https://pastebin.com/raw/BAMjeudP

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/LodgeiT/labs-accounts-assessor/issues/108?email_source=notifications&email_token=ACF4N2MTKV6Y2M2EUYLZI6LQHFF3JA5CNFSM4IPEQGHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD5SGDRY#issuecomment-526672327, or mute the thread https://github.com/notifications/unsubscribe-auth/ACF4N2I4MVSIZMLGUSGKWTDQHFF3JANCNFSM4IPEQGHA .

[koo5]

doc/piecharts

[koo5]

issues with quicksight: no way to import data programmatically, only on once-a-day schedule. reference: https://docs.aws.amazon.com/quicksight/latest/APIReference/API_Operations.html https://stackoverflow.com/questions/44206485/refresh-aws-quicksight-automatically

Programmatic embedding into our reports is possible.

very nice quicksight "alternative": https://docs.knime.com/latest/analytics_platform_quickstart_guide/index.html

[koo5]

todo turn this issue into a wiki page

[koo5]

If I'm to visualise, 3 Pie Graphs as standalone, then Asset vs. Liability & Equity

Optimally drill-down to explore lower realms of the taxonomy or if you like, further from the trunk.

doc/piecharts files added

Earnings Bar Chart, Unrealised Gains & Realised Gains. Earnings Bar Chart, Unrealised Losses & Realised Losses. Also, Legend with dates - AS AT or FROM TO, & AUD rounded to the thousands. Andrew, 10:43 AMThere are lots more useful reports and probably it will be great if accountants have a syntax to do this themselves via some API that is responsive to a simple accounting language profile i.e. show pie/line/bar graph of Assets -Liabilities=Equity. System should reject illogical combinations like Assets + liabilities.... & suggest a fix Assets - liabilities.And in an accounting system, when something is wrong it is possible for a human make a range of inferences in order to discover what is wrong and make an error correction. Human will explore a range of learned possibilities of what cause accounting systems to fail.You saw it with an entry that had too many sales.

[koo5]

https://opensource.com/business/16/11/open-source-dashboard-tools-visualizing-data

[koo5]

we need to focus on exporting all data that might be useful (probably in json).

[koo5]

tried https://freeboard.io - too limited widget selection, but that could probably be amended by spending some resources on their development. Knows how to define a json file url as a source, and runs a server that re-fetches the file at configurable intervals, and in a widget add screen, provides autocompletion dropdown for selecting source and drilling down through json keys. Resultant widget datasource configuration is a js expression, so arithmetic etc is possible but not in a nonprogrammer-friendly way. We would probably run our own server and auto-generate "users" or "dashboards" for each endpoint request.

[koo5]

There are lots more useful reports and probably it will be great if accountants have a syntax to do this themselves via some API that is responsive to a simple accounting language profile i.e. show pie/line/bar graph of Assets -Liabilities=Equity. System should reject illogical combinations like Assets + liabilities. possibly excel/powerpoint/? would actually be the right medium here? Beyond that, we are in the realm of defining and forever maintaining DSLs. Not that we would not have the right tools to start with that, but how do we define the scope, with limited resources? ...

[koo5]

Can there be a magnitude in a flow i.e. most days you bank $100, then on rare occasion you bank $1,000. Is that magnitude?

[sto0pkid]

"Can there be a magnitude in a flow i.e. most days you bank $100, then on rare occasion you bank $1,000. Is that magnitude?" yes in the context of vector-based interpretations the $100 and $1,000 are magnitudes (of vectors representing rates, i.e. "amount banked per day")

[koo5]

Some useful graphs - Ratio of outflows through a time frame. I E all things that credit banks, normalised to AUD. Vs. Ratio of inflows through time. Put in the dollar values normalised to AUD. So to normalise, you'll need to show a gain or a loss in currency. Currency gain shows in the inflow pie while a currency loss shows in the out pie. 31.08.19 09:32:18 What we want to develop is a toolkit for constructing financial reports. There are too many possible future permutations of report for you to build them all especially as we want to move forward with some matrix based accounting and graphs. So please consider providing syntax level instruction and information snippets i.e. the output of the accounting logic engi ne that can be leveraged by report developers.

[koo5]

possibly a promising direction:
some projects revolving around https://www.w3.org/TR/vocab-data-cube/
which is a data schema similar to xbrl, and we should be able to automatically generate conforming data.

dead a couple of years:
    https://github.com/AKSW/cubeviz.ontowiki
        demo docker running, not sure how to use. but i'm curious about ontowiki itself too.
    JS rewrite, only a few features:
        https://github.com/AKSW/cubevizjs
    and it leads us to:
        http://opencube-toolkit.eu/
        also dead.

https://zazuko.com/
    https://github.com/zazuko
    https://github.com/zazuko/data-cube-frontend-example
        no visualization, just shows how to work with the datacube lib
    https://editor.zazuko.com/
        collaborative ontology editing

https://github.com/lorenae/qb4olap-tools
    extensions to the data cube ontology
    " we propose a high level OLAP language, called QL, which consists on a set of well-known operators: rollup, drilldown, slice, and dice. Using the cube metadata, also written using QB4OLAP, we automatically generate SPARQL queries to implement sequences of QL operations."
    broken demo, build with node 6 or 8, and try to find a dataset.

other:
    simple data model (backend) library:
        https://github.com/feonit/olap-cube-js

or back to "standard BI" tools:
    https://github.com/d4software/QueryTree

[koo5]

for trying qb4olap-tools: sudo snap install node --channel=8.6.0 --classic for trying ontowiki-cubeviz: docker run -p 8080:80 -p 8890:8890 aksw/dld-present-cubeviz

[koo5]

https://www.stardog.com/trainings/accessing-stardog-data-with-power-bi/