Excel is a great tool for quick, ad hoc analysis of data. However, business users often embed entire end-to-end processes in Excel because of the ease of making instant changes to the data, calculations, and outputs. The usual issue in replacing Excel-driven processes is that business requirements constantly change and users need to represent those changes within a few hours, not the few weeks it can take for delivery by an engineering team.
A standard solution proposed for reducing reliance on Excel is to replace Excel VBA with Python. However, without a fundamental rethink of the process flows – using for example the pandas library instead of Excel formulae for data analysis – the only real change is the programming syntax. A Python solution (e.g. from the Openpyxl library) would still use Excel’s object library (via COM) to produce automations within Excel; and the processing would still occur within Excel, such as refreshing pivot tables, recalculating formulae etc. Python scripts can be version controlled in a Git; however this can also be true of Excel add-ins written in VBA, using Git hooks. It is even possible to use VSCode as an IDE for VBA projects, rather than using Excel’s embedded VB Editor. Without a rethink of the overall process, a change of programming language is not a strategic solution to the underlying data control issues.
A strategic solution would include:
- All data persisted in a database, where it is better controlled, more secure, and can be analysed more easily;
- Data flows clearly defined and configurable;
- Transparent, reviewable, and version-controlled business logic;
- Reporting represented in an interactive data visualisation application, such as Power BI.
I have not yet encountered an off-the-shelf solution that solves the spreadsheets problem; and I think that such a product and supporting services would have enormous demand.
A Business Logic application between a data warehouse and Excel could visually represent the business logic, enabling non-technical users to:
- Define and apply secondary calculations;
- Join and group data (with controls in place to avoid dropped and duplicated rows);
- Filter and configure output datasets;
- Order the process flows sequentially.
An Excel add-in could call the resulting reports to Excel for review and analysis. The add-in could enable ad hoc data (resulting from the review) to be uploaded into the Business Logic app, such as adjustments and new types of reference data. All ad hoc data changes would then be tracked and made transparent, rather than obscured in the logic of an Excel workbook.
Over time, machine learning would have a greater role in suggesting and optimising the business logic.
The aim is for smart money help available to everyone.
Smart rules enable optimisations and automations of a person’s finances, all in one place from a single app – including saving, borrowing, payments, investments, and trading.
The community aspect enables performance measurement and sharing of the best Smart rules.
A version of the Sentiment Ratio applied to EURUSD H1 has been automatically updating twitter feed @TSSMarkets.
The Sentiment Ratio indicator gauges current Market Sentiment between -100% and 100%, where lower than -40% is considered Bearish and higher than 40% is Bullish. The channel between the two is considered Neutral.
The indicator has been a very useful additional filter tool for market signals.
The Multi Trendlines indicator automatically draws the dominant trendlines on the current chart.
The indicator looks for the 5 best Up trends and the 5 best Down trends as at the current price, drawing each trendline if filter conditions are met.
The price relative to the trendline values could be used as an additional filter tool for market signals.
The Trendline Syncing System (TSS).
Positions are based on the best entry and exit points in the rhythmic movements of trending markets, evaluated across multiple timeframes, applying aggregated Analyst pull ratios, Sentiment Ratio analysis, and economic news event actuals versus forecasts.
Over the past year I have experimented with three different methods of trading in the currency markets: computer automated algorithmic trading, human manual trading, and semi-automated algorithmic trading.
Purely automated trading – using pattern recognition and data mining – has proven to be very unsuccessful, which is is to be expected, as it is highly unlikely that there exists a magic formula for technical analysis that can be applied at all times. If such a thing existed, the designer of the magic money box would quickly become the richest person in history, given the trillions of dollars that pass through the financial markets every day. Successful automated algorithms in the markets compete on speed of response to price imperfections, and to variances between market expectations and economic data announcements.
Manual trading has had variable returns, yielding a slight net gain, but this is no guarantee of future performance due to the lack of a reusable fully codified strategy.
The most successful method is the hybrid combination of a human and a machine working together in real-time, which has performed very well over the testing time period. The human (me) innovates and modifies ideas from large volumes of data, processed by the algorithm, as directed by me. The semi-automated algorithm then dispassionately executes the trading plan in the lifespan of the strategy, instantly and precisely processing every market tick, every moment of every day.
WIBAMU Easy Accounts is an Accounting software that interfaces with Microsoft Excel, enabling automation and easy control of your accounts.
Easy to use – no need for software training or knowledge of accounting.
The ease and flexibility to customize WIBAMU to the Management Accounts you require makes this application a powerful solution for a small to medium-sized business or for your home accounts.
Users often have requirements to download data from their accounting software into Excel so that they can reuse the data in their business processes. WIBAMU Easy Accounts has been developed for direct use with Microsoft Excel because it is quicker, easier and more effective if the data is controlled in just one place.
What else makes WIBAMU Easy Accounts different from other Accounting software?
- WIBAMU automatically determines the correct accounting entries from your chosen options. No knowledge of double-entry bookkeeping is required.
- To save time, multiple data journals can be posted simultaneously in one bulk upload.
- Reports can be configured as you require and summary numbers can be drilled-down to the journal level to view the underlying dataset.
- All data can be exported and imported via csv files in a standardized format for sharing or interfacing.
- You can split any data journal values between Analysis fields as you define. For example you could split the journal between business lines, locations, P&L drivers, attribution etc.
- Any required details and reference information can be added to a data journal as needed. You can set-up your own drop-down lists of defined values in Settings and apply groupings and sub-groupings to provide meaningful management information for you and/or your business.
- You can create your own fields in the reports with any lookup data you wish to include.
- The Help Window at the bottom of each screen shows relevant guidance as you use WIBAMU Easy Accounts.
- Quickly shows your profit, loss, income and costs at any time.
- Shows the financial position of your business.
- Shows the key ratios needed to understand the drivers and performance of your business.
- Automatically calculates the correct double-entry bookkeeping for any data posting.
- Controls ensure the accounts (debits and credits) always balance.
- Accounting journals can be posted by bulk upload or with templates to save you time.
- Enables consolidation of different business lines or distributed processes.
- Creates fully attributed and analyzed P&L, customized as you require.
- Enables trend analysis, comparatives analysis, forecasts versus actuals, debtor and creditor analysis, cash flow details, stock review and other flexible reports with groupings and drill-downs on the data.
- Also can be used for producing home accounts, debt balances and budgets.
- Easy-to-understand help information is provided in a support window as you use the application.
What are the benefits of leveraging Excel functionality?
- WIBAMU Easy Accounts can be easily integrated into any existing Excel-based processes.
- Excel is widely used by businesses – it is used to share data in common formats that people understand.
- The WIBAMU data structure can be easily reconfigured to include new reference fields and analysis splits.
- Flexible reports can be refreshed and reconfigured quickly and easily as required.
- Users can group, drill-down, sort, filter, find, review and update the data quickly.
- Powerful analysis and representation tools can be used in Excel on the data.
- Processes within an Excel workbook can be transparent rather than hidden in software logic.
- Excel enables Cloud-based sharing of the data and processes, with interactive commentary.