2014 Fourth International Conference on Communication Systems and Network Technologies

Power Distribution System Load Flow using Microsoft Excel

MKS Sastry, SMIEEE Randy Roopchand Roshan Chhetri

University of West Indies, University of Trinidad and Tobago College of Science and Technology
St Augustine Campus Trinidad and Tobago Royal University of Bhutan
Trinidad and Tobago [email protected] Bhutan
[email protected] [email protected]

Abstract—This paper presents a novel tool based on is also known as backward and forward load flow (BFLF)
Microsoft Excel for analyzing radial distribution systems. method. This method is also known as ‘feeder wise Load
New algorithms are developed to draw the network Flow’ and indeed is excellent to carry out the analysis for
diagrams graphically within the spreadsheet, once the input each feeder in the entire system. In other words, each
data is available. A 9-bus radial distribution system is feeder is treated separately as a single network.
considered for demonstrating the features of the developed
tool. Computations and results are verified with an existing A. Load Flow Studies
benchmarked tool. Network loading analysis is carried out In this method, the computations begin from the
on the sample 9-bus system and results were provided to extreme load end side of the network (from the back) and
demonstrate the effectiveness and usefulness of this tool. powers are calculated backwards till the source point
(usually the substation, where the HV line terminates or
Keywords-Power Distribution Networks, Radial Networks,
MS-Excel, VBA, Network Topology, Load Flow Analysis
feeds into the distribution feeder). Then voltages are
computed in the forward direction starting from the source
(HV substation node) and terminate at the far end nodes of
I. INTRODUCTION the network. After a few iterations network voltages will
Power Systems consist of High Voltage Transmission remain constant within a set tolerance just as in the case of
and Low Voltage Distribution networks. These two are HV load flow techniques. Convergence is achieved once
distinctly different from each other. Distribution networks the voltages (and angles) at all the nodes remain within the
work span more kilometers of length when compared to set tolerance limit. The detailed treatment and analysis of
their transmission counterparts. They are usually this method is not presented here to avoid repetition.
concentrated in a specific area or two, whereas HV
B. Issues with Existing Software Packages
networks connect regions which are quite apart from each
other. Distribution networks connect customers to the Several popular software solutions are commercially
power network and HV networks transport power from available such as Power System Simulator for
generation site to the distribution networks. Engineering (PSS/E), PowerWorld etc for the analysis of
Voltages, power flows and section losses are computed power networks. These applications are mainly for high
in the steady state, using the well-known technique ‘Load voltage power system studies, not necessarily for power
flow’[5]. The voltage range for power distribution distribution networks. There are several disadvantages
systems usually start from as low as 110Volts (or with the commercial software packages. Firstly, they are
220Volts) and go up to 33kV. Most distribution networks expensive and not suited for classroom learning, since
are radially operated and are characterized by higher ratio these software packages provide only the final results but
of resistance (R) to reactance (X) ratio (R/X). On the not the intermediate calculations. Secondly, these
contract, high voltage transmission networks are usually products are very complex and hence involve longer
operated in ring (or mesh) and have lesser ratio of R/X learning curves for the new users and also product
compared to distribution networks. This makes the case configuration and customization can be expensive. Most
for using separate load flow methods, which take
universities use MATLAB to teach power system studies.
advantage of the features such as mesh configuration and
low resistance of individual sections. This paper focuses However, the licensing system of MATLAB is proving to
specifically on the treatment of distribution networks using be expensive since toolboxes still have to be purchased
Microsoft-Excel. separately. Programs still require the MATLAB platform/
application for execution.
II. DISTRIBUTION SYSTEMS
C. Use of Spreadsheets in Power System Studies
Several load flow methods have been proposed for
On the contrary to using the commercial applications,
distribution networks, but this paper uses the popular
spreadsheet based solutions are very promising as such
method that was proposed by Haque[3], and this technique

978-1-4799-3070-8/14 $31.00 © 2014 IEEE 954

DOI 10.1109/CSNT.2014.196
applications provide alternative, sustainable and further analysis of the system using the developed tool.
economical solution. The first advantage is that, in most Table 1 shows the bus data and Table 2 shows of the line
cases the data of the power distribution system will be data this 9-bus system. The network can be seen in
generally available in spreadsheets. And hence, a figures 3 and 4.
spreadsheet based solution is more appealing. Then, the
final results of the power flow study, if available in a
spreadsheet, can be processed more easily. In other
words, spreadsheet based solutions do not require data
transformations from one form to the other. With the
advent of Microsoft technologies and ‘Visual Basic for
Applications’ (VBA) for MS Excel, spreadsheet programs
have achieved wider acceptance due to their inherent user
friendliness and windows look-alike functionality.
Applications based spreadsheets can be effectively used
in universities and in utilities to reduce the burden of
maintaining expensive software products and their
licenses. Another obvious advantage of spreadsheet
based solutions is the portability between the computers
without any hassle.

In fact, many applications have been developed for Figure 1. Front Cover with the Main Menu.
power system studies, but mostly for High Voltage Power
System Studies[1,2,4,7]. Lau and Kuruganty[2] provided TABLE I. BUS DATA OF 9 BUS SYSTEM
a lucid treatment of theory, algorithms and even
spreadsheet implementation. Their implementation used Load( Load Voltage
Node
kW) (kVAR) (kV)
cell formulae and only two methods – Gauss-Seidel and 1 0 0 12
N-R methods have been presented. Then Sastry and 2 0 0 12
Ricardo[1] presented a single spreadsheet based solution 3 17 12.5 12
for the three popular iterative (Gauss-Seidel, Newton- 4 17 12.5 12
5 17 12.5 12
Raphson, Fast Decoupled NR) and non-iterative (DC) 6 17 12.5 12
load flow methods. This specific contribution provides 7 17 12.5 12
interactive screens for user inputs, detailed load flow 8 17 12.5 12
output, and even a comparative analysis of all the 9 17 12.5 12
methods. However, this contribution mainly focuses on
TABLE II. LINE DATA OF 9 BUS SYSTEM
HV power system studies, but not on distribution
networks. Line From To Bus Length R/kM X/kM
No Bus (kM)
III. MS EXCEL BASED DISTRIBUTION SYSTEMS LOAD 1 1 2 1.4 0.1 0.085
2 2 3 1.49 0.1 0.085
FLOW 3 2 4 1 0.1 0.085
Our MS-Excel sheet is very simple, user-friendly and 4 3 5 0.86 0.1 0.085
does not require any specialized learning. The front sheet 5 3 6 1.27 0.1 0.085
6 4 7 1.53 0.1 0.085
of the file presents the main menu with various options to 7 7 8 0.59 0.1 0.085
the users such as data input, output, generate the load flow 8 7 9 0.78 0.1 0.085
output and to view the network connectivity. A screen 1 1 2 1.4 0.1 0.085
shot of the front cover with the main menu is shown in
Figure 1.
B. Implementation of Forward and Backward Load
A. Input Flow
BFLF (or any distribution load flow technique) Data is provided in the MS-Excel document, in the
requires line parameters such as resistance per kilometer, ‘Input’ sheet. BFLF is implemented using the VBA using
reactance per kilometer, charging admittance, and length a series of functions and stored in the same document.
of the line etc. Usually this data is provided in terms of When users click the button ‘Execute Load Flow’, then
the well-known ‘from-bus’, ‘to-bus’ format. Then node the corresponding VBA function goes through the input
data consists of load details at each node. For the purpose data and computes the solution over iterations.
of demonstration, a sample 9-bus radial distribution is
considered to explain the implementation of BFLF and

955
C. Output built, Bus 6 and 7’s data position may have been re-
The intermediate results are written to a sheet called ordered and as such, we would have lines crossing each
‘Run time’, so that users can go through the values for other unnecessarily. The relative horizontal spacing
verification purposes. The final results are provided in ensures proper ordering to eliminate crossing lines and
the sheet ‘Output’, in terms of nodal voltages, power ensure a neater view.
losses and flows in all the sections of the network.
D. Single Line Diagram BEGIN
There are two types of single line diagrams – simple line
diagram with horizontal and vertical lines showing the
Get Number of levels in tree
network connectivity; the other one is the vector diagram
which can be modified to represent the physical layout of
the network (however, this requires further editing and Calculate width of tree (widest portion, i.e
processing) to match the GPS coordinates. greatest number of nodes)

E. Algorithm for the Graphical Single Line Diagram

Get Number of levels in tree
From node – to node information is gathered into data
structure. A typical data structure is chosen, which is
similar to that of a tree. The tree is composed of n levels.
Calculate relative horizontal spacing and
These levels are stored in an array, each with the node
relative vertical spacing
that is the head bus and a pointer to the head of a linked
list of data values, which contain information for each bus
that is connected to this node (we refer to these as the
Calculate node position (tier,
branches of the tree). Some important considerations in
horizontal sequence)
the logic building are listed below:

1. Composing the tree involves additional tables to track Draw Node

parent and child busses – This is built as a lookup table
to find out which level each bus sits at.
Get Parent Node
2. The tree is built from the data provided - Nodes are
entered in an iterative manner, which involves locating Draw Branch
which level they sit at (by looking up its parent node in
2 above) and by appending at the end of the appropriate
linked list (branch of tree).
No
All nodes
3. Levels are not unique – there can be multiple entries for drawn?
the same level. The point of this is to maximize
viewing space on the final graphic. As can be seen in
Figure 6 - Bus 5, 6 and 7 are displayed neatly at the Yes
same level for viewing, but Bus 7 belongs to a different END
parent node than Bus 5 and 6.
While an array was used for level information, to reduce Figure 2. Algorithm for generating Network Diagrams
complexity, another linked list can be used to achieve
further advantages with respect to dynamic memory 2. Determination of Relative Vertical Spacing: The
allocation. In depth details on aspects such as vertical vertical viewing space is considered practically endless
spacing, horizontal spacing, (x,y) position on the as the user is expected to scroll downwards if the view
spreadsheet are avoided as those are out of scope of this does not capture busses that sit at the base of the tree.
present discussion; and a summary of the approach is For this reason a fixed value is normally used for the
provided below. vertical spacing. The relative vertical spacing is used
to determine if the entire structure can be shown in a
1. Determination of Relative Horizontal Spacing: The one/page view.
relative horizontal spacing is simply the number of
nodes at each level and the order in which they should 3. Determination of screen coordinates: Since the display
appear. For instance, depending on how the tree is settings can be different for any individual machine

956
 being run on, the values for the x and y resolution (say
1024 by 768) are found by a standard system call.
Using these values, the real positions as in x and y on
the actual screen are calculated (for example the first
node is positioned at 320, 20 – midpoint of the screen
and at the top since it is the first node).

4. Determination of angles for the tree: After deciding

where the nodes are located, they are then drawn. For
the vector diagrams, the nodes are simply connected at
the mid points to form the branches. Since the nodes
are positioned neatly across the viewing space, the
angles for the branches do not need to be calculated.
The results give us neat looking diagrams. The line
diagram is similarly drawn, however, instead of simply
joining the node mid-points as in the vector diagram,
lines with 90 degree bends are utilized. In addition to
drawing the branches, circuit breakers are drawn at Figure 4. Vector Diagram for the 9-bus system
each node on the line diagram and can be set on/off.
These states are stored in the bus node information
structure. To determine which parts are energized a Our program makes an assumption that each section
simple inheritance rule is applied to busses and the has two sets of circuit breakers and their states need to be
values updated. provided (0-off and 1-on) in the ‘Input’ sheet alongside
the line and load values. The connectivity diagrams are
The overall algorithm for drawing the line and vector automatically drawn in sheets ‘Line_Diagram’ and
diagrams is shown in figure 2. ‘Vector_Diagram’. In any event, user changes any circuit
breaker position to zero (off) then networks can be re-
generated by clicking the ‘Re-Draw Network Diagram’.
The classical single line diagram as generated by the User will be prompted with the message as shown in 5.
spreadsheet application is shown in figure 3.

Figure 5. Message after regenerating the diagrams and Load Flow

In this case, networks are re-drawn, but the parts of

network that were switched-off will be shown in red color
to indicate the status. Figure 6 shows the single line
diagram of the 9-bus system, when the section between
bus 2 and bus 4 is switched off
Upon the selection of ‘Execute LF’ or ‘Re-draw’
buttons, not just the networks are re-drawn and load flow
Figure 3. Single Line Diagram for the 9-bus system
is also done, but only for the energized parts of the
network. This makes the case for an effective use of this
spreadsheet for further planning studies and comparative
The vector diagram as generated by the spreadsheet
analysis.
application is shown in figure 4. It may be noted that,
.
these diagrams can be edited or modified as needed to
represent the actual topographical connectivity to enhance
the diagrammatic representation of the network.

957
 Figure 8. Comparison of kW flow in each section of 9-bus system with
different LF techniques

Figure 6. Single Line Diagram for the 9-bus system, when some
sections switched-off

IV. BENCHMARKING THE RESULTS

Accuracy of the computations and implementation of
algorithms is utmost important in Power System
Studies[6,9]. For this purpose, we evaluated the 9-bus
system with the classical load flow studies and compared
the results to the BFLF method that is presented here.
First, the data of the 9-bus network is analyzed with the
MS-Excel based tool contributed by Sastry and
Ricardo[1]. This tool was benchmarked against InterPSS Figure 9. Comparison of kVAR flow in each section of 9-bus system
with different LF techniques
and PSS/E for IEEE-14 and IEEE-30bus standard
systems[6]. Hence, it is good enough to use this tool as
standard and to verify the results produced by current tool
for the distribution networks. Figures 7, 8, 9 and 10 show
the comparison of various computations and results of the
9-bus system, with using both the tools.

Figure 10. Comparison of kW losses in each section of 9-bus system

with different LF techniques

Figure 7. Comparison of Voltages of 9-bus system with different LF

V. USING THE MS EXCEL TOOL
techniques This MS-Excel sheet can be freely downloaded from
the author’s skydrive at the url: http://sdrv.ms/InjRUN

958
A. Utility Engineers calculations. Furthermore, with some tweaking, students
The utility distribution system will have several can set their convergence criteria and look at the
substations and each substation will have several feeders performance of different load flow iterative methods,
with appropriate names. Since each feeder needs to be based on different loading schemes. This approach gives
analyzed separately, this excel sheet should be made the student an appreciation for the real-world
multiple copies with different names reflecting the names consequences to some of the engineering choices made.
of the feeders to avoid confusion. Then the data of the
VI. CONCLUSION
feeder needs to be transferred into the ‘Input’ sheet and
then simply execute the load flows to see the output. A simple, yet novel tool based on MS-Excel for power
Planning studies can be done as needed by varying the distribution network analysis is presented. The BFLF
length of the lines, R and X values of the lines (for method is implemented using VBA in MS-Excel platform.
different types of conductors) and of course for different The network is drawn automatically to provide
loading conditions. The output is available in each case in connectivity between the nodes in two standard formats to
the ‘Output’ sheet for further, necessary processing as assist the users in understanding the topography of the
needed. To demonstrate the effectiveness of this tool, the network. The computations are successfully
load on the 9-bus system is varied from base value to 10 benchmarked for the verification of accuracy, with a
times the base value, in multiples of two. The variation of similar, properly benchmarked excel application. The
the voltage at all the buses is shown in figure 11. For results are satisfactory. From the discussion, it can be seen
instance, the line for 4x corresponds to four times the that this tool can be effectively used by both utility
original load at every bus. engineers for planning studies and by students for learning
purposes.

REFERENCES
[1] M.K.S. Sastry and Ramkhelawan, Ricardo B. (2012) "Power
System Load Flow Analysis using Microsoft Excel," Spreadsheets
in Education (eJSiE): Vol. 6: Iss. 1, Article 1.
http://epublications.bond.edu.au/ejsie/vol6/iss1/1/
[2] Lau, Mark A. and Kuruganty, Sastry. (2008). “Spreadsheet
Implementations for Solving Power-Flow Problems.” Electronic
Journal on Spreadsheets in Education (eJSiE), Vol.3: Iss. 1, No. 3
[3] Haque, H. (1996), “Efficient load flow method for distribution
systems with radial or mesh configuration”, IEEE Proc on
Generation, Transmission, Distribution, 143(1): 33 – 38
[4] Xu, W., Lui, Y. Koval, D. and Lipsett, M.A. (1999). “Using
Spreadsheet Software as a Platform for Power System Analysis”,
Figure 11. Voltage variation of 9-bus system for different loadings IEEE Transactions on Computer Applications in Power.
[5] D. P. Kothari and I. J. Nagrath (2008),“Modern Power System
Analysis” New York, McGraw Hill.
[6] IEEE (Institute of Electrical and Electronics Engineers, Inc.) Std
B. Classroom Teaching 399-1997. IEEE Recommended Practice for Industrial and
Iterative methods for load-flow calculations are Commercial Power Systems Analysis.
normally taught using a sample distribution network, [7] N. D. Rao and N. Y. Haddad, “Typical Applications of New
Generation Spreadsheets To Power System Problems”, IEEE
alongside the generic equations governing each iterative Transactions on Power Apparatus and Systems, 1991, pp. 159-162.
step. While the proposed tool helps to bring across the [8] Microsoft (2013), “System requirements for Office 2010”,
general idea, it is generally intensive for students to http://technet.microsoft.com/en-us/library/ee624351.aspx; Last
manually calculate by hand for the entire network and Accessed November 2013.
analyze results at each step consistently. It also makes it [9] Gutierrez, J.F.; Bedrinana, M.F.; Castro, C.A. (2011), “Critical
tedious and error-prone to check for convergence. comparison of robust load flow methods for ill-conditioned
Our load flow excel sheet developed can assist in systems”, IEEE Int. Conf on Power System Technology
(POWERCON), Trondheim , Norway
students’ understanding, by allowing students to quickly
manipulate the data given, visualize how the network
looks (diagrams generated) and have a sense of the results
of the calculations at each step involved (runtime values).
These results may also be used verify the hand

959