Transcript Mail merge MadE easy - NeighborWorks America

Data Analytics
Using Pivot Tables to Analyze Portfolios
ANNUAL CONFERENCE
KANSAS CITY, MO
MAY 5, 2015
2
ABOUT OCCH
OCCH is an independent nonprofit corporation governed
by a Board of Directors, founded 1989
OCCH’s mission is: “to cause the construction,
rehabilitation, and preservation of affordable housing” –
Ohio and Kentucky are primary focus
Primarily a syndicator of Low Income Housing Tax Credits
• $3 Billion in Equity Investments
• 625 Affordable Housing Projects
• 32,000 Affordable Housing Units
3
IS YOUR DATA NORMALIZED?
• If using any typical database (YARDI / RealPage / etc.)
the answer is probably YES
Edgar Cobb – inventor of ‘relational model’
4
IS YOUR DATA NORMALIZED?
If you study the ‘tables’ contained in any good
system, it will appear that they don’t provide much
useful information.
The data is there…but it is likely spread across many
tables…making data appear unruly!
If appropriately ‘normalized’, data is ‘deconstructed’
into distinct parts.
5
Proj#
YrID
MnthID
Occ
23
28
3
42
23
28
2
41
47
28
2
97
6
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
7
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
8
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
9
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
10
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
11
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
12
Proj#
YrID
MnthID
Occ
YrID
Year
MnthID
Month
23
28
3
42
25
2012
1
January
23
28
2
41
26
2013
2
February
47
28
2
97
27
2014
3
March
28
2015
4
April
Proj#
ProjName
NumUnits
BuildTypeID
ConstTypeID
23
Happy Homes
45
4
1
47
Apple Grove
100
1
3
BuildTypeID
BuildingType
ConstTypeID
ConstructionType
1
High-Rise
1
New Construction
2
Mid-Rise
2
Mod Rehab
3
Single Family
3
Rehab
4
Townhouse
4
Historic Rehab
42/45 =
93.3%
13
YOU PROBABLY NEED A QUERY OR VIEW
ProjName
Construction
Type
Building
Type
State
County
Mgmt
Company
Year
Month
Units
Occ
Occ%
Happy Homes
New
Construction
Townhome
OH
Summit
Good
Mgmt Co.
2015
January
45
42
93.3%
Apple Grove
Rehab
High-Rise
OH
Franklin
Good
Mgmt Co.
2015
March
100
97
97.0%
14
GETTING YOUR DATA INTO EXCEL
• There may be readily available ‘queries’ or ‘views’ that
can be easily output from the database in .xls
• IF NOT, you may need to write a query to assemble the
necessary data into a usable format
• If you ‘do it yourself’, you will need access to the
database tables…consider using MS Access to connect
to tables and write your own query
• If too technical, seek assistance from a tech person
asking that a query be written to extract the desired
data into a flat .xls file
15
THINKING ABOUT DATA FREQUENCY
While it is technically possible to merge data with different
frequencies into a single query output, I discourage this
practice…things get very difficult to manage. Keep it
simple!
• MONTHLY occupancy reports
• QUARTERLY financial reports
• ANNUAL financial audits
16
PIVOT TABLES: POWERFUL BUT DANGEROUS!
• Pivot tables are exceedingly powerful but it is very easy
to NOT UNDERSTAND or mis-interpret the results…
• ALWAYS VALIDATE YOUR FINDINGS!
• As a matter of practice, I always cross-check results to
verify results
17
LET’S DO IT!
THANK YOU!
Tony DiBlasi
Chief of Asset Management
Ohio Capital Corporation for Housing
[email protected]
(614) 224-8446 x157