Exporting GnuCash Data to PowerBi

Some things are better if you do them yourself. I mainly did this project to keep a running ledger of the changes that I would need to keep track of the account balance data.

GnuCash is great, but when I export my accounts data the CSV file isn’t easily translated with power-query automatically. I decided that because I need to keep track of my transactions, this problem was best suited with a little program to calculate my net inflows and outflows using a python program, I also decided halfway through the project that I wanted to hook it up to my graphing backend, a silly idea – but a fun one to see my spends go up and down. I decided that I’d publish it here so that in the future I would find it a lot easier.

Traditionally if you wanted to export GnuCash data from CSV to PowerBi, you’d be better off using their inbuilt power query, however, I wanted to implement a ledger system, something that I don’t think can be accomplished directly in PowerBi without some scripting, and in the future I want to be able to change platforms if I need to because I no longer have a licence for PowerBi or want to use something else like excel or free equivalents like Google Sheets. I reckon that if done properly Google Scripts could make everything run automagically from an upload, but I don’t have my reports ready yet for that to happen.

# Extracted from here https://github.com/aidancrane/GnuCash-CSV2CSV-for-PowerBi/blob/master/convert_to_powerBI.py

import csv
from re import sub
from decimal import Decimal
import time


# If we only want the transactions values, set this to true and they will print to console, they do not save to accounts_out.csv!
numbersOnly = False

# Anyone thats not me will want this to be false, this is used to show the transaction data on a live graph I use for scratching around with.
smoothieChartEmulation = False
sessionCookie = "39494goodluckguessingthispartlololol213232expiresanyway"

# Leave this here so that Notepad++ and Atom auto-suggest it.
# Date,Account Name,Number,Description,Notes,Memo,Category,Type,Action,Reconcile,To With Sym,From With Sym,To Num.,From Num.,To Rate/Price,From Rate/Price

if (smoothieChartEmulation):
     import requests

# Negative Numbers are bracketed when exported from GNUCash so we need to fix that for the float data type.
def convert_if_negative(number):
    returnNumber = str(number)
    if ("," in returnNumber):
        if ("(" in returnNumber):
            returnNumber = returnNumber[1:]
            returnNumber = returnNumber[:-1]
            returnNumber = returnNumber.replace(",", "")
            returnNumber = 0 - round(float(returnNumber), 2)
        returnNumber = str(returnNumber).replace(",", "")
    if ("(" in returnNumber):
        returnNumber = Decimal(sub(r'[^\d.]', '', returnNumber))
        return (0 - round(float(returnNumber), 2))
    return returnNumber

# open accounts.cvs, our exported file.
with open("accounts.csv", "r") as csvIn:
    reader = csv.DictReader(csvIn)
    entries = []

    runningTotal = float(0)
    
	# Save
    for row in reader:
        if (row["Account Name"] == ""):
            pass
        else:
            runningTotal = runningTotal + float(convert_if_negative(row["To Num."]))
            if (numbersOnly):
                print(str(round(runningTotal, 2)))
            else:
                if (smoothieChartEmulation):
                    payload = {'random_graph': runningTotal}
                    r = requests.get('https://dash.infinityflame.co.uk/dash/flex.php', params=payload, cookies={'PHPSESSID': sessionCookie})
                print(str(convert_if_negative(row["To Num."])) + " Description: "+ row["Description"] + " Account Balance: " + str(round(runningTotal, 2)))
                entries.append([row["Date"],row["Description"],row["Category"],str(convert_if_negative(row["To Num."])),str(round(runningTotal, 2))])

# Save what we care about to our new csv for power BI   
with open('accounts_out.csv', mode='w', newline='') as csvOut:
    titles = ["Date","Description","Destination","Transaction","Account Balance"]
    writer = csv.writer(csvOut, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    writer.writerow(titles)
    for transaction in entries:
        writer.writerow(transaction)

I used python because it’s quick to debug in Atom and I know it well. The way this snippet is used is to export an account from GnuCash, then use this program to output the final CSV to be taken in by PowerBi, I’ve chained these steps together. The final result is stored in accounts_out.csv.

Thanks!

Business Processes Re-engineering

When a business grows and adapts it often requires a lot of overhauls, both physically and digitally. Processes that previously worked or were effective for purpose are no longer so. Because of this a business will re-engineer part of its business processes to adapt.

Reasons Why a Business May Adapt its Processes

A business may change the way it functions for many reasons,

  • The business is expanding and it’s old process needs to handle the additional capacity or machinery.
  • The function the old system used to serve has been superseded or removed from the business process as it is no longer required.
  • The business is being merged or acquired by another and duplicate processes need to be removed or increased and vice versa.
  • The process currently being used is not dynamic enough to be useful anymore or is causing issues.
  • A process is failing or is not always effective.
  • The current process has too many avoidable errors.

Reasons Why a Process May Need Changing

A business may change it’s processes for many reasons, such as ; –

  • Improve the speed of operation.
  • Improve the customization or dynamic of a process.
  • Improve the continuity or compatibility to connect the process to other processes.
  • As a reaction to changing legislation, laws or consumer complaints.
  • Improve the efficiency of a process.
  • Improve the operating cost of a process.
  • Improve the output or capacity of a process.
  • Improve the working capital investment in a process.
  • Improve quality of a product.
  • Change the product.

Re-engineering a process not only requires identifying a change but enacting that change can take a lot of steps to complete, especially for products in areas that are heavily reliant on compliance and conformity, such as electrical appliances or medial tools.

Improving Efficiency Caveats

Changing a business process can be risky and should have appropriate risk analysis in place where processes are crucial to the business process or jobs.

Many businesses re-engineer their processes simply to ensure that their product or products match demand. Overhauling business processes may not be financially viable if the number of defects or failing products outweighs the cost of replacing the machinery or software process.

Improving the speed of a process may make the process faster but the business may also want to change the system implemented, such as using a batch process system where the job can be completed as part of a ‘batch’. Having many batches however can lead to greater working capital on complex components or products and can mean that if there is a problem with one then it is likely that the whole batch is also going to fail. Batch processing can also make quality control and quality assurance much harder and more expensive to enact.

Reliable internet connections. Are they a reality in Britain yet?

Most would say no. However, the recent increase in consumer spending on reliable mobile and the domestic internet has led the development of new infrastructure, innovation, and government subsidy. The increased value of fast and affordable internet is no doubt due to the increase in cord-cutting and streaming options available to the consumer.

However, there are still issues in rural broadband areas where internet connections are limited, spotty or oversubscribed and need infrastructure investment in order to improve. The biggest issue could be the degradation and limited ability of domestic subscribers to ensure a reliable connection, many using dated routers, cables and positioning WiFi access points where there are many obstacles such as walls, corners, and doors making the signal weaker and less effective and often situated at one side of their home, rather than in the centre or where the WiFi is being used. Many new routers are combating this problem by using more antennas and user-friendly configuration wizards or combating the problem entirely by using pre-configured settings from the supplier or ISP.

Another problem may be the proximity to the exchange, as most subscribers in rural areas find their connection vastly slower compared to that of locations nearby, once again showing a performance drop that could be improved by moving away from copper to more effective methods of transmission, such as fiber.

It is however clear that the infrastructure as a whole has vastly improved and is much more capable as it was, mobile network connectivity has also improved to handle the data needs of modern apps and streaming websites as per the March report.

The issue as the report outlines is, however, meeting the demand of mobile business subscribers and providing affordable internet options to consumers. In my opinion, the use of ADSL2+ and DOCSIS 3.1 is not as much of a step in the right direction as fiber, even if it means limiting the speed available to the subscriber to segment their pricing structure.

Source, written by me in 2017.

 

Aspects a business should consider in corporate strategy

Corporate Strategy

 

The way a company’s managers should manage a company should largely be down to the business plan. However there are often external factors limiting a companies business, they may choose to solely benefit shareholders, but practically and legally they must provide some benefit to their employees and their customers to remain socially responsible and have a strong positive public relationship to remain profitable. An umbrella corporation may often have to make strategic decisions to combat potential problems well in the future. In the show Silicon Valley (external) the senior venture capitalist spends a lot of his time studying sesame seed markets in Myanmar and Brazil in order to profit in the future, this example (although fictional and a little overplayed) is a small look into the world of ensuring that profits are maintained and some of the crucial detail that must be taken into account.

The ability to assess the viability of consolidating corporate strategy is not only about turning a profit, but is also about financial and shareholder relations, the capability and flexibility of dynamic and slow moving markets, the technical ability of the employees and manufacturing capabilities as well as cost, the people and manpower involved and the risks undertaken at every stage.

Ensuring that a business has a strategic plan ensures that the company has direction and vision, companies that fail to innovate or differentiate could get left behind or never reach their true potential. A business must define the scope of its operations, how it defines its goals, then how it plans to achieve them, and finally, evaluate. Companies that fail to find a suitable corporate strategy could be left behind and not identify important challenges ahead of time and their current states failings such as their branding or vision.

Aspects a business should consider in corporate strategy.

  • Brand appearance, awareness.
  • Internal Culture
  • Market Segmentation
  • Product, Pricing, Placing, Promotion
  • After sales
  • Research and Development
  • Growth Strategy
  • Human Resources
  • Product and Market Innovation
  • Technology

There are many techniques that businesses use to maintain a strategic advantage, such as Porters strategic matrix (external) and Ansoff’s matrix.

Factors Influencing Demand

A Demand and Supply Graph

Demand and Supply Graph

Factors Influencing Demand

This article serves to supplement the article Influences for Supply and Demand, that I wrote a while ago. For a more general overview of supply and demand, I suggest visiting there first.

Demand has many factors that influence a market, such as;-

  • Changes to the prices to substitute products, such as cheaper products available online or at discount stores could reduce the sales of a premium product or product range.
  • Changes to the availability of complementary goods or changes in the price of complementary goods, such as two large luxury opposing ice cream companies.
  • Disruptions to the supply chain of competitors, such that their prices or other aspects change.
  • The incomes of customers could rise, causing customers to choose superior or premium products, similarly, they could fall and choose less or inferior products, Especially prominent for Elastic products or Luxury Items.
  • Advertising could increase consumer interest or make the product more noticeable to customers or potential customers.
  • Branding or PR could be used more prominently or advertised.
    • Recently companies have opted for using social marketing or guerilla marketing, as it can be more successful or noticeable since ad-blockers and the fall of cable TV and TV Advertising have made it harder to reach consumers.
  • Advertising or Branding of other competitors products could also potentially boost your own profits, especially if the product is generic or seasonal.
  • Changes to consumer incomes.

The demand for a product or service may also depend on the demographics of the product, if it is aimed at older people and the area is a student town, finding customers may be more difficult than other locations.

External Shocks, such as the economy or new technologies that make the product or service obsolete could also negatively affect the products demand if a business fails to innovate or adapt to change.

Factors Influencing Supply

A Demand and Supply Graph

Demand and Supply Graph

Factors Influencing Supply

This article serves to supplement the article Influences for Supply and Demand, that I wrote a while ago. For a more general overview of supply and demand, I suggest visiting there first.

Supply has many factors that influence a market, such as;-

  • The availability of raw materials.
  • The time it takes to process a set, batch or quantity of stock, like aged wine or hard sweets.
  • Disruptions to the distribution chain of competitors, such that their prices or other aspects change.
  • Natural Disasters utility supply issues or storage issues.
  • Advertising could increase consumer interest or make the product more noticeable to customers or potential customers and cause a shortage due to unforeseen interest or popularity.
  • Branding or PR could be used more prominently or advertised to cause similar consequence.
  • New Machinery or Technology could make the production of items much less time consuming or much cheaper such as new methods of batch processing.
  • A monopoly may artificially limit the availability or raise the price or perform anti-competitively.
  • Automation could speed up production and therefore increase availability.
  • Government Subsidy or Tariffs could increase or decrease supply in a domestic market.
    • A subsidy devised to increase sales in a companies domestic market could raise consumer interest.
    • A company may be forced to maintain a domestic market orientation as shipping externally may not be competitive due to tariffs.

The overall effectiveness of a product is readily available to be supplied by manufacturers depends on consumer interest, the availability of raw materials and the lead time it takes to produce a product.

Agricultural products may be much harder to maintain during droughts or other natural disasters as they take the time to grow naturally and no amount of investment can speed up the process much more than what people are willing to pay for.

Assessing Company Value

When individuals are interested in purchasing, investing or working with a company it may also be a good idea to assess it’s financial status, using public information available through government sources, two documents can allow proper assessment. A statement of comprehensive income and a statement of financial position.

Company value calculations

Statement of comprehensive income

A statement of comprehensive income is a usually detailed report listing the inflows and outflows of capital of a business. They often include exceptional expenses and net profit for a business and subsequently can be difficult to follow but should provide a good understanding on how a business functions through the financial year.

Statement of financial position

A statement of financial position outlines the inventories, assets and equity a business has at the end of a year or season. It may also help to identify where most of a businesses cash is being spent or held in assets.

What are the benefits of these documents?

A statement of comprehensive income will help to identify the basic operations of a business, for example the yearly turnover. A company that makes more turnover than another, however may not be financially better or worse however as it can depend on how the money is used, fortunately there are many equations that can help us to define a standard for these figures that we can use to compare between businesses or yearly statistics.

Gross Profits

Gross profits allow us to determine how much profit a company makes after selling it’s products, this is already better than revenue as it shows that a company may be making a lot of revenue, but may be spending it all on sales. It is usually defined in the statement of comprehensive income.

Gross profit is Sales Revenue – Cost of Sales.

This figure is useful, but can be more effective when coupled with a percentage, as it is would allow us to compare for example previous years income, as below, it is for this reason we use the Gross Profit Margin to calculate a percentage.

Gross Profit Margin is (Sales Revenue – Cost of Sales) ÷ Sales Revenue. Higher is better.

Business Gross Profit Gross Profit Margin
XYZ Telecoms $470,000 51.32%
AB Telecom $460,000 79.92%
AT TEL $430,000 30.10%

As you can see from above the company XYZ Telecoms makes more money than AB Telecom however it is much more inefficient in it’s spending and therefore could reduce the cost of it’s sales to get higher returns. Note that although AB Telecom makes a higher Gross Profit Margin it does not make more money. Here is a breakdown of another Telecom company Telecomatic and they spend roughly 40% of their Revenue on sales, and therefore have a gross profit margin of 60%. In other words, if 40% is spent on sales, the company keeps the other 60% barring any other costs, like fixed costs.

Revenue Cost of Sales Gross Profit Gross Profit Margin
$100,000 $40,000 $60,000 60%

Using gross profit and the gross profit margin is advantageous because it;

  • Enables you to calculate if spending on sales is too great.
  • Identify which company is better at sales spending.
  • Enables to identify if year on year a businesses spending on sales has been successful or not.
  • If there are two company chains, in two different locations with similar sales revenue, if they were to use different methods of advertising the sales profit margin would allow you to determine the best method based on the higher percentage.

However it does not;

  • Account for exceptional items or income.
  • Account for Fixed costs, such as machinery rent or leasing.

Operating Profits

Operating Profits are the next step of a businesses true income, operating profit accounts for overheads (expenses) that a business has to pay in order to function, expenses like factory ownership or employee pay and should easily be very close to a businesses true profit for the year if the business is new or homogeneous in nature. Operating profits however do not account for or should not account for;

  • Exceptional expenses, like purchasing new facilities or machinery.
  • Assets a business may hold of value.
  • Creditors.
  • Financial income or company dividend.

Operating Profit is calculated by Gross Profit – Overheads. Operating Profit Margin is calculated by (Operating Profit) ÷ Sales Revenue.

Yell Tell This year
Sales Revenue $848,100
Sales Expenditure $298,100
Gross Profit $550,000
Gross Profit Margin 54.20%
Expenses $130,000
Operating Profit $420,000
Operating Profit Margin 28.30%

Net Profit Margin

Lastly, for the comprehensive income sheet, the Net Profit Margin will show the overall effectiveness of the businesses expenditure. And represents how much raw profit a company makes based off its revenue, to put this into perspective its how much money is profit when multiplied by the turnover, so if a company’s net profit margin is 17%, and they turnover $1m a year, the company will have $170,000 unconditional capital left over at the end of the year barring complications.

The Net Profit Margin is calculated by Profit for the year ÷ Sales Revenue.

Critical Path Analysis

Critical Path Analysis has ties with corporate strategy and human resources. It enables a business to gain a competitive advantage by effectively planning their time to pursue a goal. This can be especially important when they are trying to be first to market or when the process requires a lot of routine steps. It is also relevant in the computing sector and neural networks.

Detailing the steps involved can help to create a better picture of the complexity of a project. To do this, Critical Path Analysis Diagrams are used to break down the project into stages.

Critical Path Nodes

Each stage is made up of nodes, and each node is made of three numbers, the nodes are the circles in the image above (there are two) and have a path between them, Each path has the project that will be completed on the top and the time on the bottom.

Each node has a number on the left half, the earliest start time on the top and the latest finishing time on the bottom. Some diagrams also denote the direction of the path, however, it usually starts on the left, and ends on the right.

Some Critical Path Analysis Diagrams can become complex and take a long time to complete, some stages may also require few or many steps to complete, it is for this reason that Critical Paths are also shown on a diagram.

Critical Path with Four Nodes

In the diagram above, there are four nodes and therefore more work to be completed, however having a top and bottom section has so far proven redundant. It is only when there is an alternate path that these nodes become useful.

Critical Path Analysis Network with a denoted Critical Path

We can now see that having the latest finish time has meant that the project now cannot be finished until the ninth day as the task ‘Air Transport’ Requires a longer time period than the other tasks. This is why the fourth node now reads ‘9’ on the latest finish time. Additionally, there is now dashes along the Critical Path as this task must be completed in order for the project to remain on time.

Critical Path Advantages

Finding the critical path of a project allows a business too;

  • Conduct better contingency planning as they can see clearly where projects have the potential to get held up in the process and aren’t slowed down due to failures in the system.
  • Optimize workflow for employees as they will be able to better manage the time they need to complete a task, should a task have a long lead time, the task could be delayed without affecting the progress of the project, for example, a node having a quicker ‘earliest start time’ than other tasks.
  • Allows a job to be completed in the shortest amount of time possible.
  • It can help to reduce risk on projects that have complex time dependencies.

Critical Path Disadvantages

  • However, it does not account for the likelihood of failure of a task where it was poorly planned from the beginning.
  • It does not account for the fact that a project may eventually have stages that need changing as time progresses or situations change.
  • It depends on the accuracy of the projects predicted time-scale.
  • It may not account for other factors like cost or trade-off.
  • Resources may not be as flexible in the long term as previously planned.

Using Government to Control Multinational Corporations

Multinational Corporations have a great influence on a lot of people, this is beneficial because it provides jobs and increases GDP, trade, and economy, however it does mean that businesses have a great deal of power, which when coupled with factors like political influence, pressure groups and the media can make a company very powerful, able to control potentially thousands of jobs and staff wages.

Common Pressure Influence

Pressure groups, though not always an insider in government can have a great deal of credibility in preventing the control from multinational corporations. Pressure groups may also incorporate radical action and anti-corporation tactics, such as driving slow through London streets or protesting on a runway. However, Pressure Groups tend to focus on the ethical activities of organisations over preventing monopolies or unfair business practices, however, there are exceptions, the majority of this work is undertaken by unions.

Consumer action by boycotting products, spreading awareness on unethical factors or irresponsible activities can make a product less profitable or cause a business to re-think its stance on a particular product or practice, however, this form of activism can sometimes have no effect on the performance of a product due to its massive success or meager opposition.

Using Government to Control Multinational Corporations

The Government is arguably the most important factor in controlling MNC’s, from maintaining fair work arrangements, breaks, pay and holiday to preventing global price fixing and implementing transnational tariffs to protect local business and economy, the government to some companies could be seen as enemy number one. It is necessary to control MNC’s to manage their growth and influence in a country. Similarly a multinational may move abroad to save money on things like manufacture, however, this is bad for local jobs, it may increase profitability for an MNC. For this reason, it is sometimes probable that a government may subsidise the company to prevent it moving abroad.

governmentvsmncs

However some massive MNC companies have profits each year greater than entire country GDP’s, this could be seen as a failure of the government to control its businesses. Finally, although MNCs need control they do provide massive benefits to a country in trade, prosperity and GDP that make them worthwhile today.

Access Control in Daily IT Organisation Tasks

Many Businesses use IT to manage their accounts, documents and decision-making. It is, therefore important that Access Control be implemented in Organisations to prevent unwanted modification or prying eyes from being able to commit computer crimes, such as the ones outlined in the Computer Misuse Act. Using Access Control can prevent these people and operational staff from being able to modify information that otherwise is not their place to edit. Some common implementations of Access Control could be limiting the information available to a customer about Transaction Processing Systems or Management Information Systems not allowing Managers access to manufacturer prices.

Access Control in Strategic, Tactical and Operational Management

In order to implement these features a common method of maintaining strict control is through a permissions model, where it is outlined to the computer what permissions a login has access to, such that they are able (like a file system) to edit, read or write a file or piece of information. Here are some common examples of Access Control;

  • A Supermarket Employee is not able to alter the price of products.
  • A Manager is not able to create new users for a MIS (Management Information System).
  • A DSS (Decision Support System) is not able to commit to a higher level of privilege without presenting documentation proving that that decision is possible, a good example of this could be a bank requiring an account number to confirm that the account is active before allowing the employee to make changes or a support agent requiring a pin from a customer before being allowed to view the customers details.

Strategic Operational and Tactical in Access Control

The three levels of control is a common (but not de-facto) model for systems management, however often these levels of tasks can become obscured by other factors. These tasks can often be divided up among IT departments in formal organisations, such as ‘Ops’ and ‘Licencing’. The use of Access Control can be used to coordinate effective ICT teamwork on large projects and in other departments, such as accounting.