Sheet

The idea is to have a module which allows us to make software representation of a spreadsheet.

The spreadsheet has a currency linked to it and also multi currency behavior, it also has powerful extra/intrapolation possibilities.

A sheet has following format

If we have 60 months representation (5 year), we have 60 columns

• rows, each row represent something e.g. salary for a person per month over 5 years
• the rows can be grouped per tags
• each row has 60 cols = cells, each cell has a value
• each row has a name

A sheet can also be represented per year or per quarter, if per year then there would be 5 columns only.

There is also functionality to export a sheet to wiki (markdown) or html representation.

offline

if you need to work offline e.g. for development do

export OFFLINE=1

Macro's

!!sheet.graph_pie_row sheetname:'tfgridsim_run1' 
    rowname:'revenue_usd'
    period_type:quarter 
    title:'a title'

• supported_actions:
• 'sheet_wiki'
• 'graph_pie_row' = pie chart for 1 row
• 'graph_line_row'
• 'graph_bar_row'
• 'graph_title_row'
• 'wiki_row_overview'

Properties to use in heroscript

• rowname string - if specified then its one name
• namefilter []string - only include the exact names as secified for the rows
• includefilter []string - to use with tags filter e.g. ['location:belgium_*'] //would match all words starting with belgium
• excludefilter []string
• period_type PeriodType - year, month, quarter
• aggregate bool = true - if more than 1 row matches should we aggregate or not
• aggregatetype RowAggregateType = .sum - important if used with include/exclude, because then we group
• unit UnitType
• title string
• title_sub string
• size string
• rowname_show bool = true - show the name of the row
• description string

fn array2float(list []int) []f64

fn array2int(list []f64) []int

fn float_repr(nr_ f64, reprtype ReprType) string

represent a

fn playmacro(action Action) !string

fn sheet_get(name string) !&Sheet

get sheet from global

fn sheet_new(args SheetNewArgs) !Sheet

get a sheet has y nr of rows, each row has a name each row has X nr of columns which represent months we can do manipulations with the rows, is very useful for e.g. business planning params: nrcol int = 60 visualize_cur bool //if we want to show e.g. $44.4 in a cell or just 44.4

fn sheet_set(sh &Sheet)

remember sheet in global

enum PeriodType {
	year
	month
	quarter
	error
}

enum ReprType {
	number // will use k, m, ...
	currency
}

enum RowAction {
	add // add rows
	substract
	divide
	multiply
	aggregate
	difference
	roundint
	max
	min
	reverse    //+1 becomes -1
	forwardavg // try to find 12 forward looking cells and do avg where we are
}

enum RowAggregateType {
	unknown
	sum
	avg
	max
	min
}

pub enum RowType{ cur integer float }

enum UnitType {
	normal
	thousand
	million
	billion
}

struct Cell {
pub mut:
	val   f64
	row   &Row @[skip; str: skip]
	empty bool = true
}

fn (mut c Cell) set(v string) !

fn (mut c Cell) add(v f64)

fn (mut c Cell) repr() string

fn (mut c Cell) str() string

struct Group2RowArgs {
pub mut:
	name          string
	include       []string // to use with params filter e.g. ['location:belgium_*'] //would match all words starting with belgium
	exclude       []string
	tags          string
	descr         string
	subgroup      string
	aggregatetype RowAggregateType = .sum
}

struct Row {
pub mut:
	name          string
	alias         string
	description   string
	cells         []Cell
	sheet         &Sheet @[skip; str: skip]
	aggregatetype RowAggregateType
	reprtype      ReprType // how to represent it
	tags          string
	subgroup      string
}

fn (mut r Row) action(args_ RowActionArgs) !&Row

add one row to the other

''' name string optional: if not used then row will be modified itself action RowAction val f64 optional: if we want to e.g. multiply every cell with same val rows []Row optional: a row if we want to add each val of item of row, can be more than 1 tags string how to recognize a row (selection) aggregatetype RowAggregateType is unknown, sum, avg, max, min delaymonths int //how many months should we delay the output descr string subgroup string ''' row action is ''' add // add rows substract divide multiply aggregate difference roundint max min reverse //+1 becomes -1 forwardavg // try to find 12 forward looking cells and do avg where we are '''

fn (mut r Row) cell_get(colnr int) !&Cell

fn (mut r Row) copy(args_ RowCopyArgs) !&Row

fn (mut r Row) delay(monthdelay int) !

pub fn (mut r Row) add(name string, r2 Row) !&Row { return r.action(name:name, rows:[]r2, tags:r.tags) }

fn (mut r Row) extrapolate(smartstr string) !

smartstring is something like 3:2,10:5 means end month 3 we start with 2, it grows to 5 on end month 10 . the cells out of the mentioned ranges are not filled if they are already set . the cells which are empty at start of row will become 0 . the cells which are empty at the back will just be same value as the last one . currencies can be used e.g. 3:10usd,20:30aed (so we can even mix) . first cell is 1, the start is 0 (month 0) . if the smartstr, is empty then will use existing values in the row to extra/intra polate, the empty values will be filled in

fn (row Row) filter(args_ RowGetArgs) !bool

apply the namefilter, include & exclude filter, if match return true

fn (r Row) look_forward_avg(colnr_ int, nrcols_ int) !f64

starting from cell look forward for nrcolls make the average

fn (r Row) min() int

fn (mut r Row) recurring(args_ RowRecurringArgs) !&Row

fn (mut r Row) smartfill(smartstr string) !

something like 3:2,10:5 means end month 3 we set 2, month 10 5 there i no interpolation, all other fields are set on 0

fn (mut r Row) values_get() []f64

struct RowActionArgs {
pub mut:
	name          string
	action        RowAction
	val           f64
	rows          []&Row
	tags          string
	descr         string
	subgroup      string
	aggregatetype RowAggregateType = .sum
	delaymonths   int // how many months should we delay the output
}

struct RowCopyArgs {
pub mut:
	name          string
	tags          string
	descr         string
	subgroup      string
	aggregatetype RowAggregateType = .sum
}

struct RowGetArgs {
pub mut:
	rowname       string   // if specified then its one name
	namefilter    []string // only include the exact names as secified for the rows
	includefilter []string // to use with params filter e.g. ['location:belgium_*'] //would match all words starting with belgium
	excludefilter []string
	period_type   PeriodType // year, month, quarter
	aggregate     bool             = true // if more than 1 row matches should we aggregate or not
	aggregatetype RowAggregateType = .sum // important if used with include/exclude, because then we group
	unit          UnitType
	title         string
	title_sub     string
	size          string
	rowname_show  bool = true // show the name of the row
	description   string
}

struct RowNewParams {
pub mut:
	name          string
	growth        string
	aggregatetype RowAggregateType
	tags          string
	descr         string
	subgroup      string
	extrapolate   bool = true
}

struct RowRecurringArgs {
pub mut:
	name          string
	tags          string
	descr         string
	subgroup      string
	aggregatetype RowAggregateType = .sum
	nrmonths      int              = 60
	delaymonths   int // how many months should we delay the output
}

struct Sheet {
pub mut:
	name     string
	rows     map[string]&Row
	nrcol    int = 60
	params   SheetParams
	currency currency.Currency
}

fn (mut s Sheet) cell_get(row string, col int) !&Cell

find row, report error if not found

fn (mut s Sheet) cells_width(colnr int) !int

find maximum length of a cell (as string representation for a colnr) 0 is the first col the headers if used are never counted

fn (mut s Sheet) data_get_as_list(args RowGetArgs) ![]string

return e.g. "'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'Y6'" if year, is for header

fn (mut s Sheet) data_get_as_string(args RowGetArgs) !string

return the values

fn (mut s Sheet) filter(args RowGetArgs) !Sheet

use RowGetArgs to get to smaller version of sheet

fn (mut s Sheet) group2row(args Group2RowArgs) !&Row

find all rows which have one of the tags aggregate (sum) them into one row returns a row with the result useful to e.g. make new row which makes sum of all salaries for e.g. dev and engineering tag

fn (mut s Sheet) header() ![]string

return array with same amount of items as cols in the rows

for year we return Y1, Y2, ... for quarter we return Q1, Q2, ... for months we returm m1, m2, ...

fn (mut s Sheet) header_get_as_list(period_type PeriodType) ![]string

return e.g. "'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'Y6'" if year, is for header

fn (mut s Sheet) header_get_as_string(period_type PeriodType) !string

return e.g. "'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'Y6'" if year, is for header

fn (mut s Sheet) json() string

fn (mut s Sheet) row_delete(name string)

fn (mut s Sheet) row_get(name string) !&Row

find row, report error if not found

fn (mut s Sheet) row_new(args_ RowNewParams) !&Row

get a row with a certain name you can use the smart extrapolate function to populate the row params: name string growth string (this is input for the extrapolate function) aggregatetype e.g. sum,avg,max,min is used to go from months to e.g. year or quarter tags []string e.g. ["hr","hrdev"] attach a tag to a row, can be used later to group smart exptrapolation is 3:2,10:5 means end month 3 we start with 2, it grows to 5 on end month 10

fn (s Sheet) rowname_get(args RowGetArgs) !string

get one rowname, if more than 1 will fail, if 0 will fail

fn (s Sheet) rownames_get(args RowGetArgs) ![]string

find rownames which match RowGetArgs

fn (mut s Sheet) rows_description_width_max() int

walk over all rows, return the max width of the description field of a row

fn (mut s Sheet) rows_names_width_max() int

walk over all rows, return the max width of the name and/or alias field of a row

fn (mut s Sheet) toquarter(args ToYearQuarterArgs) !Sheet

make a copy of the sheet and aggregate on quarter params name string rowsfilter []string tagsfilter []string tags if set will see that there is at least one corresponding tag per row rawsfilter is list of names of rows which will be included

fn (mut s Sheet) toyear(args ToYearQuarterArgs) !Sheet

make a copy of the sheet and aggregate on year params name string rowsfilter []string tagsfilter []string tags if set will see that there is at least one corresponding tag per row rawsfilter is list of names of rows which will be included

fn (mut s Sheet) values_get(name string) ![]f64

fn (mut s Sheet) wiki(args_ RowGetArgs) !string

fn (mut s Sheet) wiki_bar_chart(args_ RowGetArgs) !string

produce a nice looking bar chart see https://echarts.apache.org/examples/en/index.html#chart-type-bar

fn (mut s Sheet) wiki_line_chart(args_ RowGetArgs) !string

produce a nice looking bar chart see https://echarts.apache.org/examples/en/editor.html?c=line-stack

fn (mut s Sheet) wiki_pie_chart(args_ RowGetArgs) !string

produce a nice looking bar chart see https://echarts.apache.org/examples/en/index.html#chart-type-bar

fn (mut s_ Sheet) wiki_row_overview(args RowGetArgs) !string

fn (mut s Sheet) wiki_title_chart(args RowGetArgs) string

struct SheetNewArgs {
pub mut:
	name          string = 'main'
	nrcol         int    = 60
	visualize_cur bool   = true  // if we want to show e.g. $44.4 in a cell or just 44.4
	curr          string = 'usd' // preferred currency to work with
}

struct SheetParams {
pub mut:
	visualize_cur bool // if we want to show e.g. $44.4 in a cell or just 44.4
}

struct ToYearQuarterArgs {
pub mut:
	name          string
	namefilter    []string // only include the exact names as specified for the rows
	includefilter []string // matches for the tags
	excludefilter []string // matches for the tags
	period_months int = 12
}