Each Time I Tell People

If you do the Twitter thing, follow me at @HousingITguy
or LinkedIn here https://uk.linkedin.com/in/tonysmiththathousingitguy

This week I encountered in a conversation, a reference to a ‘database’, in an organisation with few specific application systems. On further investigation, (a simple, “Can you share your screen, so I can see your database”), all is revealed. What is being described is an Microsoft Excel Spreadsheet.

Now before we get fully into this post, I am not Excel-ist (or even spreadsheet-ist, if there are any Lotus SmartSuite users out there – Respect!). I am a big fan of Excel, but not for storing important data, like in a database.

I was considering just the other day, if I had £50 for every spreadsheet and each time I tell people, I had described as “not a database” or said, “stop using that spreadsheet as a database fella (he/she OBVs)”, I would have a substantial pension annuity built up by now. So many data manipulation functions have been added to Excel over a few decades, it provides a deceptive veneer. Add to that, the problem that 90% or more of spreadsheet users, have never had any formal training at all, just adds to the problem. Staff just in some cases do not understand that rows are best set up to be entities (eg each row a property, case, rent account etc, a column a particular value or field of a specific type).

Excel in the big maths scheme of things...

Many spreadsheets I see, could actually be easily transferred to Microsoft Word, with very little effort, with many people not even noticing, if a few tables were incorporated.

One big reason I hear from client staff, is that they maintain a spreadsheet because they can protect the data from the barbarians outside. In doing so, they become a silo, (although they are unlikely to twig that).

Reliance on Excel, often wastes a considerable amount of time and effort, often keeping certain staff members expert in their own silos.

My job is usually, (in the event that staff have not twigged it as yet!), is to extoll the virtues of a real database or application based on one and in most cases, transport them there as fast as possible.

So why migrate away from Excel? In contrast to Excel, databases allow users to only enter data once, as it offers the functionality to link other records together. Clearly, we might record a single asset and multiple complaints or anti-social behaviour events against it. Spreadsheets struggle with this kind of stuff.

When Excel contains more than a few hundred rows, users experience significant slowdowns. Databases utilise indexes and other means of speeding up access and reporting. Audit is generally easier too, where data can be (sometimes accidentally) lost in traditional spreadsheets. Many rows can trip up the biggest enterprises at times too!

Excel does provide sharing of a kind, although its not quite up to a proper database and often causes more problems than it actually solves. I have used Excel sharing occasionally successfully when with others scoring supplier demos, but you wouldn’t rely on running any organisation critical processes on it, believe me.

If you are reading this from a small organisation, running exclusively on Excel, you are probably overdue in giving me a call. Many small social housing providers do run on spreadsheets, many under 200 units in size and typically doubt that anyone like me would be interested in helping them organise better. In the last year, I have assisted housing providers managing 36, 160, 190 and 390 units, as well as the five to thirty thousand homes brigade. We can match suitable solutions to your needs.

If you are reading this from an organisation already with Integrated Housing Solutions and you have hundreds of spreadsheets still, perhaps you need help from a different direction. I have often helped teams with trust issues, or where additional useful and integrated functionality, can be squeezed out of existing solutions. Often the reasons for not using system modules (often already purchased and being paid for…), are sometimes tenuous. Convenience for a single team, can be over-valued beyond wider organisation integration and efficiency.  

In summary, I encounter spreadsheets usually as the rock in the road to transformation, rather than an enabler. Consider how you use spreadsheets in your organisation and consider if there should be a better way.

Related Post: Get your self-service right, or maybe come back when you can

Catch the blog monthly archive at https://soundcloud.com/housingitguy/

_/_/_/_/_/_/_/_/

I would be pleased to connect with you on LinkedIn - http://uk.linkedin.com/in/tonysmiththathousingitguy Message me with any issues or queries, you would like to be explored in this blog. We generally receive a couple of suggestions each month.

Steve Gold – Each Time.
(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009


Access a quick list of our Social Housing ICT blog posts here

Could we help you or your organisation? Our contact details are here , get in touch we will be pleased to chat about your problems and help with your organisation issues.

File Under: #HomesForBritain,#HousingDAY,#InternetOfTenants,#Shout,#UKHousing, 07854655009, 1st Touch,1stTouch,360,365Agile,3squared,4Js, ACL,ALMO,AMS,AX,Aareon,Abritas,Academy,Accuserv,Accuserve,Active Housing,ActiveH,ActiveHousing,Acutence,Advanced,Agile,Agile365,AirWatch,Alfresco,Alignment,Allpay,Amazon Web Services,Anite,Apex,App,ArchHouse,Archouse,Asprey e-state pro,Asset Management,Associates,Aurora,Average IT Costs,AWI SX Integration Toolkit,AWS, BI,BO,BPR,BancTec,BigChange,Blockwise,BluTek,Bluebox,Blueprint,Browser Applications,Business Objects,Business Planning,Business Process Review,Business social networking, CACI,Capita One Housing,CBL,CCS IT Keystone CCSIT,CEDRM,CHICS,CHR,CIH,CMS,COA,CORE,CPL,CRM,CRM2013,CRS,CTI,CTX,Cadcorp,Capita,CapitaOne,Capita One,Capital Management,Cashflow,Castle,Castleton,Castleton Technology,Cedar Open Accounts,Cerrus,Change,Charges,Chartered Institute Of Housing,Cheaper Housing IT,Chics, Citrix,Civica,Civica CTX,Civica Cx,Civica Genero,Civica Saffron,Clearview,Clik,Cloud Dialogs,CloudDialogs,Coactiva,Codeman,Comino,Commontime,Community Reward Services,Company,Competitive Dialogue process,Component Accounting,Consilium,Consolidation, Consultancy,Consultant,Consultants,Contact Manager,Context,Contractor Systems,CorVu,Cost Reductions,Covalent,Crystal Reports,Customer Relationship Management,Cx, DRS,Deeplake,Designer Software,Development Systems,Director,Document,Documotive,Docuware,Dynamic AI,DynamicAI,Dynamics 365,D365,Dynamics365, ECMK,EDM,EDRMS,ERP,ESRI,Elmhurst,Enghouse Interactive,England,English,EnterpriseBI,Estatecraft,Etive,Exhibition,Exponential-e, Facebook,Factorwise,Field Service management,Finance,Financial Systems,Financials,Footprint,Forms,Freezes,Fusion, Gas Tag,G-Cloud,GCloud,GDPR,GGP,GIS,GasTag,Genero,GeoSolveIT,Getting best from,GoTonySmith,Grasp,Grip,Group Apex, HFI (Housing Financials interface),HG,HRA,Hardware,Hitachi Systems,Hitex,HomeMaster,Hometeam,HouSys,House,Housemark,Housemark survey,Housing,Housing Contact,Housing Group,Housing Insight,Housing Management,Housing Management Consultant,Housing Partners,Housing Portfolio Management System,Hub Asset Management,Homeswapper,Housing jigsaw, Housing Support Pro,HousingIT,HousingSupportPro,HyperOptic, IMS,IT,IT Budget,IT Training,ITIL,Impact Response,In,In4,In4Systems Promaster,InfoBoss,InHouse,InMotion,InMotion2015,InMotion2016,InfoBoss,Infoflow,Information,Information Technology,Information@Work components,Informix,Innovation, Informetis,Inphase,Inside Housing,Insight,Internet Portal,Internetalia,Invu,Ireland,Irish,itLab, Keyfax,Keylogic,Keypera,Keystone,Kirona,Kypera, Landlord,Ledgers,Linkedin,Liquid,Locality,Localz, M3,MAVIS,MD,MDM,MI,MIS,MIS-AMS,MISCS,MRI,MS Dynamics,MRI,MS Dynamics CRM2011,Microsoft365,microsoft 365,MWL,Management,Management Server,Manifest,Measuring,Mebus,Microsoft Dynamics 365,Microsoft dynamics GP 2013,Miracle,MobileIron,Mobysoft,Monopoly board images and pictures,Montal,Mr Void,MriEngage,MrVoid,MRI Socialhousing,MRISocialhousing, NDL,NINTEX,NINTEX workflow,NPS,NPS ASSIST,NPS Housing,NPS Job Manager Mobile,NROSH,Natural,Neighbourhoods and Communities,Nintexworkflow,Northgate,Northgate Codeman,Northgate Public Services,Notice, OA,OGC Buying Solutions,OJEU Limits,ORS,Ohms,OmFax,Omniledger,One,OneAdvanced,OneServe,Open source,OpenContractor,OpenHousing,Opti-time,Options,Optitime,Oracle,Orchard,Outsource and outsourcing,OutSystems, PIMMS,PIMSS Data,PM,PRINCE2,Paloma,Pamwin,PanConnect,People Value,Peoplevalue,PfH,Pick,PlanForm,Planned maintenance,Plenfific,Plus,Portfolio Management System,PowerObjects,Pro,Pro-Points,Prodo,Progress,Promaster,Propoints,Proval,Providers,Pyramid, QL,QuantSpark,Qube,QLX,Qlikview,QueryView,Queryview reporting,Quiss, RM865,ROCC,RPs,RSL,Rave,ReAct,Reality,RedkiteCRM,Red Olive,Registered,Registered Social,Rent Increase,RentSense,RentSenseLite,Repairfinder,Reporting,Reports,RobotAutomation,Rocket, Salesforce,Salesforce.Org,SAP,SASSHA,SDM,SHBVN,SM,SOTI,SP,SQL,SQL Open Housing,SQL Reporting,SQL Server,Saffron,Safron,Saturn,Scenario,Scotland,Scots,Scottish,Scout,Sequoa,Serengeti,Serros,Server,Service,Services,Servitor,Sharepoint,Sim,SimPro, Simdel,Simdell,Slash and Burn,Social Housing ICT,Social Housing Software Applications,Social Media,Software,Software solutions,Spotlight Service,Spotlightservice,Star rating,Stores and Stock,Strategic,SunAccounts,Sunguard,Surveys,Streetwise, Swordfish,Sx3,Symatrix Human,System alignment,Systems,Systemwise, T-Files,TEAMS,TED,TFiles,TP Tracker,TSG,Tagish Casework,Tagra,Task,TAIM,Technologies (India) PVT Limited,Telecetera,Template,Terminal Services,That,Three Star,Today,Tony Smith,Tony Smith That Housing IT Guy,TonySmith that housing IT,TonySmithHou,TonySmithHousing, ,TonySmithHousingITguy,Total,Total Mobile,Totalmobile,Trace,Tribal,Twitter, U2,UC,UK,UK Housing,Ukhousing,UniClass Enterprise,Unidata,United Kingdom,Universal,Universalcredit,Universe,Unrest, Valueworks,Van Stock,Vantage,Vantage Sentinel,Version,Virtualisation,Visualmetrics,Voice,Voice and data,Voluntas, Wales,Welsh,Wheatley,Windows Server,Workflow and,Work Hub,Works Connect, XML,Xen App,XenApp,Xmbrace, ...
. . . . . . . . . . . .

Labour Of Love

If you do the Twitter thing, follow me at @hotpixUK
or LinkedIn here uk.linkedin.com/pub/tony-smith-bsc-hons-acih/a/979/351/




Sometimes I come across some real labours of love, encapsulated in Excel. I am sure we have all come across them, multiple worksheets, using lots of formulas, function seven deep, no end of HLOOKUPs and pivot tables.

It's amazing how some of these start off as simple ways to solve quite small problems. That's how they gain acceptance and grow into some real animals. One I encountered was from an organisation managing personal care budgets. It worked fine while they were managing a few dozen of them. Being successful, they soon were awarded a few hundred more to manage and within eight months had an expanding spreadsheet with already 25,000 rows of transactions on it.

Another example I come across often, is tracking of voids. How void teams seem to love spreadsheets and the way columns can be formatted as dates. This ignores the fact that most integrated housing management systems out there can knock out a fairly functional void action plan. Spreadsheets can look sexier though and that's often the attraction.  One I was looking at with a void team recently (I won't say where) had an Excel guru who built theirs and it looked great. However to report they had to count voids manually, there were no SUM() functions in it, or conditional highlighting to graduate a cell red when it was overdue.

When it was suggested they could build the void action plan with dates in the HMS and it was explained that would be easily reportable, the conclusion floored me. The void manager said, "Oh yes, that would be just like a database!".  "Well yes dear, that's the point of an integrated HMS", I was thinking while I bit my tongue. I calmly suggested I put it on the list of areas we will note that need addressing.

...and don't get me on the number of excel hosted Gas servicing and asbestos spreadsheets I have seen, during my 19+ years in social housing. 

There is a serious skills gap around understanding Excel and also what is possible from applications users already have. The former is a problem caused by organisations, who rarely provide enough training to assist them in getting the most from a brilliant tool. Lack of knowledge of system applications that are there, to manage our business processes, sometimes comes down to lack of training, but also some chronic under-implementation.  Suppliers are as much to blame here as project teams and ICT staff. When suppliers make it so difficult to understand and configure integrated systems, it plays into the hands of the Excel lovers.

I leave you with this poem I stumbled across this week, from Brian Bilston. You will enjoy it if you know your Excel. If there are any subtleties you are missing, get yourself on an Excel course.

Feel free to get in touch if you can knock together an 'Ode to Ohms', 'A Couplet for Comino' or a 'Promaster Pentameter'. Get your stanza's out 8-)    

     Catch the blog monthly archive at https://archive.org/details/@housingitguy

Related Post: When it comes to workflow, are your users more used to their tea-tray than their HMS Task-Tray?

_/_/_/_/_/_/_/_/

I would be pleased to connect with you on LinkedIn - http://uk.linkedin.com/in/tonysmiththathousingitguy Message me with any issues or queries, you would like to be explored in this blog. We generally receive a couple of suggestions each month.


Hue and Cry - Labour Of Love.
(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009


Access a quick list of our Social Housing ICT blog posts here
Could we help you or your organisation? Our contact details are here , get in touch we will be pleased to chat about your problems and help with your organisation issues.
File Under: 360,1stTouch,4Js. , 07854655009 , ," """" ",,#UKHousing,#HousingDAY, #HomesForBritain, #Shout,1st Touch,3squared,Accuserv,Acutence,Aareon,Academy,ActiveH,AirWatch,Alignment,ALMO,Anite,Apex,ArchHouse,Archouse,asbestos,Asprey e-state pro,Asset Management,Aurora,Average IT Costs,App,Associates,ACL,Abritas,AMS,AX,aspireview, BO,BPR,Browser Applications,Business Objects,Business Planning, Business Process Review,Business social networking, Blockwise, Block wise,BI,BancTec,BluTek,Bluebox, Castle,CBL,Cedar Open Accounts,Cx,Change,Cheaper Housing IT,Chics, CHR,Citrix,Civica,Clearview,CMS , CCS IT Keystone CCSIT,Civica Genero,Contractor Systems,CORE,CorVu,Cost Reductions,Covalent,CRM,Crystal Reports,CTI,CTX,Customer Relationship Management,Cashflow, COA,Coactiva,Codeman,Comino,Competitive Dialogue process,complex IT procurements,Component Accounting, Community Reward Services, Consilium, Consolidation, Consultancy,Consultant,Contact Manager,Context,CIH,Chartered Institute Of Housing,Cadcorp,Cerrus Saturn,convergeOne,CACI,CPL, CRS Director, Deeplake,Development Systems,Accuserve,Document Management,Documotive,datasystems , Microsoft dynamics GP 2013, DynamicAI, Dynamic AI, ECMK,EDRMS,England, English,EnterpriseBI,ERP Systems,ERP,Elmhurst,Estatecraft,EDM,ESRI,Exponential-e, Finance, Financial Systems, Financials, Factorwise,Facebook ,Footprint,Fusion, Getting best from,gotonysmith, Grasp,Grip,GeoSolveIT,GIS,GGP,Genero,HouSys,Housing software,HG,Housemark survey,Housing Blueprint,Housemark, HRA,Housing Support Pro,HousingSupportPro,Housing Options, Housing Group,hotpixuk, HousingIT,hotpix,Hardware,Hitex,Housing Contact Company,Housing Management Consultant,Housing Management Consultants, Impact Response,iPayPro,implementations,iRobot,In House,InHouse,In4,InMotion, InMotion2014, InMotion2015, InMotion2016 , Exhibition, conference, Infoflow,Information Technology,Informix,inHouse,in-house workforce, Innovation,Inside Housing,Internet Portal,Internetalia, Invu,Ireland,Irish,Inphase , IT Budget,IT Training,iWorld, Innovation Group Apex,ITIL, In4Systems Promaster,IT,Insight, iSupport,iSupportPro, Keylogic,Keystone,Kirona,Kypera,Keyfax, Ledgers,Linkedin,Locality,Liquid Voice, MD ,Mr Void , MrVoid, Measuring Change,Mebus,Miracle,MISCS, mobile functionality,Monopoly board images and pictures, Montal,MS Dynamics,MS Dynamics CRM2011,Miracle Software,MDM,Management Reporting,MI,M3,MIS,MIS-AMS,Mobysoft, Northgate Codeman,Neighbourhoods and Communities, Northgate,Notice,NROSH,NINTEX,NINTEX workflow,Natural Forms,Nintexworkflow, OA,OGC Buying Solutions,Ohms,OJEU Limits,OmFax,Omniledger,Open source software,open tender,OpenHousing,Opti-Time,Oracle,Orchard,Outsource and outsourcing,OpenContractor,ORS,OneServe, PIMMS Data Systems, Paloma,performance management systems,PfH, Pick,PIMMS,pimms4communities, Planned maintenance,People Value,Plus, Peoplevalue, PM,Progress,Promaster,Proval,Pyramid,PRINCE2,prince,Pamwin,PlanForm, Propoints, Pro-Points QL,QLX,Quiss,Qlikview, Registered Providers,Registered Social Landlord,Rent Increase Freezes,Repairfinder,Reporting,Reports,ROCC,RobotAutomation, Rocket,RPs,RSL,Reality,ReAct,reidmark,RM865, Saffron,SAP,Scots, Scottish,Scenario, Scotland,SDM,sector,TonySmithHou,Safron,Server Virtualisation,servicing, Servitor,Sharepoint,Simdel,Simdell,SimPro,Sim Pro,Slash and Burn,Social Media,Software,SQL Open Housing,SQL Reporting Services,SQL reporting services,SQL Server,Star rating,Stores and Stock,Strategic Asset Management,Sunguard ,Surveys,Sx3,System alignment, TonySmithHousing, Systemwise,SM,Sequoa,Serros,SP,Scout,score,Swordfish,Serengeti,SOTI,southview,Sunguard,Service Charges,Spotlight Service, Spotlightservice, SunAccounts Template,Task,Terminal Services, tonys , tonysm , tonysmi , tonysmith, tonysmitht, gotonysmith, tableau Business Dashboards, tonysmithth, tonysmiththa, tonysmiththat, tonysmiththath, tonysmiththatho, tonysmiththathou, tonysmiththathous, tonysmiththathousi, tonysmiththathousin, tonysmiththathousing,Tony Smith That,Tony Smith That Housing IT Guy,TonySmith that housing IT guy,tonysmiththathousing, tonysmiththathousinguy,tony smith housing guy,sharepoint, share point Three Star,Today,Total repairs,Tribal,Twitter,U2, tonysmith , tonysmiththat , thathousing , thathousingIT , thathousingITguy ,TED,Tagish Casework,TP Tracker,Telecetera,Tagra,Trace, Totalmobile, Total Mobile, UniClass Enterprise,Unidata,Universal Housing,Universalcredit, universal credit, universal credits,UC,Universe,Unrest, UK, United Kingdom ,Ukhousing,UK Housing, Van Stock,Voice and data,Vantage Sentinel,Version One,Visualmetrics,Voluntas, Wales,Welsh,Windows Server,Workflow and tasking,Wheatley,Works Connect,worksConnect, Xmbrace,XML ,XenApp,Xen App .

Serco Don’t Lose That Number!

If you do the Twitter thing, follow me at @HousingITguy
or LinkedIn here https://uk.linkedin.com/in/tonysmiththathousingitguy

Earlier this week, a few thousand people and probably a dozen at least of my contacts, probably grinned with a knowing smile. 

Of course I am writing about the UK’s national Serco Test and Trace spaff-up, relying on Excel spreadsheets, to collate and process Covid-19 tests. 

If I had £500 for every time I had told people off for calling 'info in spreadsheets', a database, I could have retired by now.

The situation could be laughable, if it were not so important and crucial.

Now, if anyone wants to spread blame around, I would personally start with Microsoft. Why So, you might ask🤔

The original Lotus 1-2-3, as anyone who broke their teeth on it during the 1980’s may remember, only had a maximum of 8,192 rows (and 256 columns). Now if it had stayed at that size, a very limited number of fools would have tried to store lots of data in it and sensibly selected a proper database to do the job. But no. As Reeve’s and Mortimer might have said, “They couldn’t let it lie” -  .

Microsoft’s Excel gave us 65,536 rows, immediately giving the illusion of it being a ‘database’, not a useful summary manipulation and presentation tool, which is what a spreadsheet does best. I have been involved in so many projects where users, managers and directors in some cases, have been firmly wedded to spreadsheets, needing to migrate to proper solutions, with protection, data control and robust audit. I have often proposed amnesty, after replacement system go-live, where spreadsheets can be dropped.

My proposal is that if the fearsome and opposing communities in Northern Ireland, could put their weapons beyond use, we should have a spreadsheet version of the Good Friday Agreement, where users do the same and promise never to return to them again. For some its starting to catch on thankfully.

For sure, Excel can be a good place to start for an initial prototype, but no one should continue to try to run a whole enterprise on it. Serco are not alone. Eight years ago or so, I worked with a big organisation, dealing with £8m of monthly transactions on Excel with its one million row and memory limit creaking & ready to break. Fifteen years earlier, with a turnover a 50th of that, spreadsheets just kind of worked. For that organisation it took about 4 months to procure and 12 to implement, a proper finance, feeder and self-service solution and they never looked back.

Serco however have apparently been paid £12b of taxpayers dosh and should have been able to build their own database quite quickly with 20 or so of the right ICT staff, none of which would have probably stuck with Excel, if there was any capability of a millions of test a day Moonshot a-coming.

In my time manipulating data, I have witnessed many things Excel does trying to be clever, without telling you. Truncating to 65,536 is common where old solutions can only use XLS format to export. No doubt you might have experienced that too. The type and length of unique ID’s seen in the SocialHousing sector, is ripe for Excel to helpfully swap then to scientific notation. How we laughed at that one, several weeks down the line.

 
So, beware the spreadsheet, its not a database. Don’t do a Serco Track & Trace, learn from their mistakes.

Who in YOUR organisation needs to take that advice to heart?

Related Post: Keep an eye on your staff using spreadsheets, adding up totals, with a calculator too!

Catch the blog monthly archive at https://soundcloud.com/housingitguy/

_/_/_/_/_/_/_/_/

I would be pleased to connect with you on LinkedIn - http://uk.linkedin.com/in/tonysmiththathousingitguy Message me with any issues or queries, you would like to be explored in this blog. We generally receive a couple of suggestions each month.

Steely Dan – Rikki Don’t Lose That Number.
(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009


Access a quick list of our Social Housing ICT blog posts here

Could we help you or your organisation? Our contact details are here , get in touch we will be pleased to chat about your problems and help with your organisation issues.

File Under: #HomesForBritain,#HousingDAY,#InternetOfTenants,#Shout,#UKHousing, 07854655009, 1st Touch,1stTouch,360,365Agile,3squared,4Js, ACL,ALMO,AMS,AX,Aareon,Abritas,Academy,Accuserv,Accuserve,Active Housing,ActiveH,ActiveHousing,Acutence,Advanced,Agile,Agile365,AirWatch,Alfresco,Alignment,Allpay,Amazon Web Services,Anite,Apex,App,ArchHouse,Archouse,Asprey e-state pro,Asset Management,Associates,Aurora,Average IT Costs,AWI SX Integration Toolkit,AWS, BI,BO,BPR,BancTec,BigChange,Blockwise,BluTek,Bluebox,Blueprint,Browser Applications,Business Objects,Business Planning,Business Process Review,Business social networking, CACI,Capita One Housing,CBL,CCS IT Keystone CCSIT,CEDRM,CHICS,CHR,CIH,CMS,COA,CORE,CPL,CRM,CRM2013,CRS,CTI,CTX,Cadcorp,Capita,CapitaOne,Capita One,Capital Management,Cashflow,Castle,Castleton,Castleton Technology,Cedar Open Accounts,Cerrus,Change,Charges,Chartered Institute Of Housing,Cheaper Housing IT,Chics, Citrix,Civica,Civica CTX,Civica Cx,Civica Genero,Civica Saffron,Clearview,Clik,Cloud Dialogs,CloudDialogs,Coactiva,Codeman,Comino,Commontime,Community Reward Services,Company,Competitive Dialogue process,Component Accounting,Consilium,Consolidation, Consultancy,Consultant,Consultants,Contact Manager,Context,Contractor Systems,CorVu,Cost Reductions,Covalent,Crystal Reports,Customer Relationship Management,Cx, DRS,Deeplake,Designer Software,Development Systems,Director,Document,Documotive,Docuware,Dynamic AI,DynamicAI,Dynamics 365,D365,Dynamics365, ECMK,EDM,EDRMS,ERP,ESRI,Elmhurst,Enghouse Interactive,England,English,EnterpriseBI,Estatecraft,Etive,Exhibition,Exponential-e, Facebook,Factorwise,Field Service management,Finance,Financial Systems,Financials,Footprint,Forms,Freezes,Fusion, Gas Tag,G-Cloud,GCloud,GDPR,GGP,GIS,GasTag,Genero,GeoSolveIT,Getting best from,GoTonySmith,Grasp,Grip,Group Apex, HFI (Housing Financials interface),HG,HRA,Hardware,Hitachi Systems,Hitex,HomeMaster,Hometeam,HouSys,House,Housemark,Housemark survey,Housing,Housing Contact,Housing Group,Housing Insight,Housing Management,Housing Management Consultant,Housing Partners,Housing Portfolio Management System,Hub Asset Management,Homeswapper,Housing jigsaw, Housing Support Pro,HousingIT,HousingSupportPro,HyperOptic, IMS,IT,IT Budget,IT Training,ITIL,Impact Response,In,In4,In4Systems Promaster,InfoBoss,InHouse,InMotion,InMotion2015,InMotion2016,InfoBoss,Infoflow,Information,Information Technology,Information@Work components,Informix,Innovation, Informetis,Inphase,Inside Housing,Insight,Internet Portal,Internetalia,Invu,Ireland,Irish,itLab, Keyfax,Keylogic,Keypera,Keystone,Kirona,Kypera, Landlord,Ledgers,Linkedin,Liquid,Locality,Localz, M3,MAVIS,MD,MDM,MI,MIS,MIS-AMS,MISCS,MRI,MS Dynamics,MRI,MS Dynamics CRM2011,Microsoft365,microsoft 365,MWL,Management,Management Server,Manifest,Measuring,Mebus,Microsoft Dynamics 365,Microsoft dynamics GP 2013,Miracle,MobileIron,Mobysoft,Monopoly board images and pictures,Montal,Mr Void,MriEngage,MrVoid, NDL,NINTEX,NINTEX workflow,NPS,NPS ASSIST,NPS Housing,NPS Job Manager Mobile,NROSH,Natural,Neighbourhoods and Communities,Nintexworkflow,Northgate,Northgate Codeman,Northgate Public Services,Notice, OA,OGC Buying Solutions,OJEU Limits,ORS,Ohms,OmFax,Omniledger,One,OneAdvanced,OneServe,Open source,OpenContractor,OpenHousing,Opti-time,Options,Optitime,Oracle,Orchard,Outsource and outsourcing,OutSystems, PIMMS,PIMSS Data,PM,PRINCE2,Paloma,Pamwin,PanConnect,People Value,Peoplevalue,PfH,Pick,PlanForm,Planned maintenance,Plenfific,Plus,Portfolio Management System,PowerObjects,Pro,Pro-Points,Prodo,Progress,Promaster,Propoints,Proval,Providers,Pyramid, QL,QuantSpark,Qube,QLX,Qlikview,QueryView,Queryview reporting,Quiss, RM865,ROCC,RPs,RSL,Rave,ReAct,Reality,RedkiteCRM,Red Olive,Registered,Registered Social,Rent Increase,RentSense,RentSenseLite,Repairfinder,Reporting,Reports,RobotAutomation,Rocket, Salesforce,Salesforce.Org,SAP,SASSHA,SDM,SHBVN,SM,SOTI,SP,SQL,SQL Open Housing,SQL Reporting,SQL Server,Saffron,Safron,Saturn,Scenario,Scotland,Scots,Scottish,Scout,Sequoa,Serengeti,Serros,Server,Service,Services,Servitor,Sharepoint,Sim,SimPro, Simdel,Simdell,Slash and Burn,Social Housing ICT,Social Housing Software Applications,Social Media,Software,Software solutions,Spotlight Service,Spotlightservice,Star rating,Stores and Stock,Strategic,SunAccounts,Sunguard,Surveys,Streetwise, Swordfish,Sx3,Symatrix Human,System alignment,Systems,Systemwise, T-Files,TEAMS,TED,TFiles,TP Tracker,TSG,Tagish Casework,Tagra,Task,TAIM,Technologies (India) PVT Limited,Telecetera,Template,Terminal Services,That,Three Star,Today,Tony Smith,Tony Smith That Housing IT Guy,TonySmith that housing IT,TonySmithHou,TonySmithHousing, ,TonySmithHousingITguy,Total,Total Mobile,Totalmobile,Trace,Tribal,Twitter, U2,UC,UK,UK Housing,Ukhousing,UniClass Enterprise,Unidata,United Kingdom,Universal,Universalcredit,Universe,Unrest, Valueworks,Van Stock,Vantage,Vantage Sentinel,Version,Virtualisation,Visualmetrics,Voice,Voice and data,Voluntas, Wales,Welsh,Wheatley,Windows Server,Workflow and,Work Hub,Works Connect, XML,Xen App,XenApp,Xmbrace, ...
. . . . . . . . . . . .

Pocket Calculator

If you do the Twitter thing, follow me at @hotpixUK
or LinkedIn here http://uk.linkedin.com/in/tonysmiththathousingitguy

"Never trust anyone working on a spreadsheet, with a pocket calculator in one hand, adding up figures, to pop in a worksheet cell". 

An excellent observation that was shared with me last week. How many staff in your organisation operate like that?

In the same week, a team told me how well they managed voids on a spreadsheet, with a row per void, and what seemed like dozens of date columns. Each column was an action or a prompt of course. IE Keys handed in, passed to repairs team, started, complete in repairs, blah, blah. Each had targets (e.g. 5 days for minor void repair turnarounds etc), but there were no formulas in the spreadsheet, no intelligent highlighting, clever formulas in hidden columns doing the leg work etc.

That team also described that spreadsheet as a 'database'.

Funniest thing was that a workflow process was set up to manage this whole thing and that was "Too time consuming, to use". Apparently spreadsheets & even a board of T-Cards were quicker. Well, they could have been if that Excel spreadsheet had all kinds of fancy formulas that could quickly provide totals of voids, ones in time, out or approaching time etc. Reporting of course was a manual affair, requiring running a finger down a column, tapping numbers into a calculator etc. So as you can imagine, the spreadsheet is actually far less efficient than the workflow that should be in use.

While this is an interesting anecdote, it does make you wonder how many people are using Excel spreadsheets, without the slightest idea how they should be used. Much like an Armalite rifle should not be issued to a combat soldier unless they can strip it down and rebuild it, maybe we should not be giving users Excel if they don't actually know the first thing about it. Better to give them Word and just point them at the 'Insert > Table' tab on the ribbon. It would be about as efficient.

Many readers might remember that old initiative of the computer users driving license. Maybe we should have an Excel driving license, users need to complete, like a road theory test, before they can use the rows and columns. Who would be up for that?

When we recruit new staff, including managers and directors, should we not test their IT literacy too? I come across so many people like in this story, who carry their managers in trying to help them handle and do even elementary manipulation of Excel and other standard office tools. 

This might well get users using what we want them to use, so we have regain control over our processes and KPIs. Oh and lets ban pocket calculators if their owners have an Excel shortcut on their desktop, while we are at it. as they said for the "Good Friday Agreement", lets "Put them beyond use". We can move on efficiently then. Amen.

Related Post: So, can you staff even tackle a VLOOKUP then?

_/_/_/_/_/_/_/_/

You can link with me on LinkedIn here - http://uk.linkedin.com/in/tonysmiththathousingitguy It would be great to connect !

Kraftwerk - Pocket Calculator.
(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009

PS As usual, if there are subjects you might like me to tackle on this blog, or you could benefit from any of our services please get in touch and let me know!

You can access a quick list of blog posts here
Could we help you or your organisation? Our contact details are here , get in touch we will be pleased to chat about your problems or issue.
File Under: 360,1stTouch,4Js. , 07854655009 , ," """" ",,#UKHousing,#HousingDAY, #HomesForBritain, #Shout,1st Touch,3squared,Accuserv,Acutence,Aareon,Academy,ActiveH,Alignment,ALMO,Anite,Apex,ArchHouse,Archouse,asbestos,Asprey e-state pro,Asset Management,Aurora,Average IT Costs,App,Associates,ACL,Abritas,AMS,AX,aspireview, BO,BPR,Browser Applications,Business Objects,Business Planning, Business Process Review,Business social networking, Blockwise, Block wise,BI,BancTec,BluTek,Bluebox, Castle,CBL,Cedar Open Accounts,Cx,Change,Cheaper Housing IT,Chics, CHR,Citrix,Civica,Clearview,CMS , CCS IT Keystone CCSIT, Contractor Systems,CORE,CorVu,Cost Reductions,Covalent,CRM,Crystal Reports,CTI,CTX,Customer Relationship Management,Cashflow, COA,Coactiva,Codeman,Comino,Competitive Dialogue process,complex IT procurements,Component Accounting, Community Reward Services, Consilium, Consolidation, Consultancy,Consultant,Contact Manager,Context,CIH,Chartered Institute Of Housing,Cadcorp,Cerrus Saturn,convergeOne,CACI,CPL, CRS Director, Deeplake,Development Systems,Accuserve,Document Management,Documotive,datasystems , Microsoft dynamics GP 2013, DynamicAI, Dynamic AI, ECMK,EDRMS,England, English,EnterpriseBI,ERP Systems,ERP,Elmhurst,Estatecraft,EDM,ESRI,Exponential-e, Finance, Financial Systems, Financials, Factorwise,Facebook ,Fusion, Getting best from,gotonysmith, Grasp,Grip,GeoSolveIT,GIS,GGP, HouSys,Housing software,HG,Housemark survey,Housing Blueprint,Housemark, HRA, Housing Options, Housing Group,hotpixuk, HousingIT,hotpix,Hardware,Hitex,Housing Contact Company, Impact Response,implementations,In House,In4,InMotion, InMotion2014, InMotion2015, InMotion2016 , Exhibition, conference, Infoflow,Information Technology,Informix,inHouse,in-house workforce, Innovation,Inside Housing,Internet Portal,Internetalia, Invu,Ireland,Irish,Inphase , IT Budget,IT Training,iWorld, Innovation Group Apex,ITIL, In4Systems Promaster,IT,Insight, Keylogic,Keystone,Kirona,Kypera,Keyfax, Ledgers,Linkedin,Locality,Liquid Voice, MD ,Mr Void , MrVoid, Measuring Change,Mebus,Miracle,MISCS, mobile functionality,Monopoly board images and pictures, Montal,MS Dynamics,MS Dynamics CRM2011,Miracle Software,Management Reporting,MI,M3,MIS,MIS-AMS,Mobysoft, Northgate Codeman,Neighbourhoods and Communities, Northgate,Notice,NROSH,NINTEX , NINTEX workflow,Nintexworkflow, OA,OGC Buying Solutions,Ohms,OJEU Limits,OmFax,Omniledger,Open source software,open tender,OpenHousing,Opti-Time,Oracle,Orchard,Outsource and outsourcing,OpenContractor,ORS,OneServe, PIMMS Data Systems, Paloma,performance management systems,PfH, Pick,PIMMS,pimms4communities, Planned maintenance,People Value,Plus, Peoplevalue, PM,Progress,Promaster,Proval,Pyramid,PRINCE2,prince,Pamwin,PlanForm, Propoints, Pro-Points QL,QLX,Quiss,Qlikview, Registered Providers,Registered Social Landlord,Rent Increase Freezes,Repairfinder,Reporting,Reports,ROCC , Rocket,RPs,RSL,Reality,ReAct,reidmark,RM865, Saffron,SAP,Scots, Scottish,Scenario, Scotland,SDM,sector,TonySmithHou,Server Virtualisation,servicing, Servitor,Sharepoint,Simdel,Simdell,Slash and Burn,Social Media,Software,SQL Open Housing,SQL Reporting Services,SQL reporting services,SQL Server,Star rating,Stores and Stock,Strategic Asset Management,Sunguard ,Surveys,Sx3,System alignment, TonySmithHousing, Systemwise,SM,Sequoa,Serros,SP,Scout,score,Swordfish,Serengeti,SOTI,southview,Sunguard,Service Charges,Spotlight Service, Spotlightservice, SunAccounts Template,Task,Terminal Services, tonys , tonysm , tonysmi , tonysmith, tonysmitht, gotonysmith, tableau Business Dashboards, tonysmithth, tonysmiththa, tonysmiththat, tonysmiththath, tonysmiththatho, tonysmiththathou, tonysmiththathous, tonysmiththathousi, tonysmiththathousin, tonysmiththathousing, tonysmiththathousingg, tonysmiththathousinguy, sharepoint, share point Three Star,Today,Total repairs,Tribal,Twitter,U2, tonysmith , tonysmiththat , thathousing , thathousingIT , thathousingITguy ,TED,Tagish Casework,TP Tracker,Telecetera,Tagra,Trace, Totalmobile, Total Mobile, UniClass Enterprise,Unidata,Universal Housing,Universalcredit, universal credit, universal credits,UC,Universe,Unrest, UK, United Kingdom ,Ukhousing,UK Housing, Van Stock,Voice and data,Vantage Sentinel,Version One,Visualmetrics,Voluntas, Wales,Welsh,Windows Server,Workflow and tasking,Wheatley, Xmbrace,XML ,XenApp,Xen App .

You Got The Vlookup

If you do the Twitter thing, follow me at @hotpixUK
or LinkedIn here uk.linkedin.com/pub/tony-smith-bsc-hons-acih/a/979/351/

Wow, I have often joked that if I had charged everyone over the past 20 years £50 for every VLOOKUP problem in Excel I have ever solved, I would have my pension, right there. It would be a vast sum. However the easy going nature I have does not permit me to be anything else than helpful and sympathetic, whenever anyone asks me to solve one.

For a customer I have been doing lots of Excel manipulations lately and from the mark its left on me here is my contribution to VLOOKUP peace in the world.

Excel is great and VLOOKUP / HLOOKUP are in the premier league of functions to be mastered. Also probably the cause of many people giving up functions forever. Well, that's sad and if that's you, please I implore you, read on....

In this article I use both VLOOKUP and HLOOKUP interchangeably.

It seems simple, here's one I wrestled with earlier:
       =VLOOKUP(A2,Sheet2!C1:F10,22, TRUE)

So, the first hurdle is not going with the default, 'TRUE'. Spandau Barry tried to persuade us things could be 'True', but here it means 'find the best match you can'. OK if that's what you want, but on the whole it wont be. False, means do a direct match. Swap it now.

Check your values (either the one you are trying to match OR the area you are trying to find it) dont suffer from trailing spaces. You wont see them in the spreadsheet, only when you edit a cell and notice the cursor is not exactly at the end of your value to search. Use Trim() to remove any excess space characters.

Leading zeros in text formatted cells may also cause you problems. IE '009089' will not match as '9089'. This can happen a lot where data such as rent account IDs are infact textual, but look like numbers (ie having leading zeroes). Try multiplying by 1 or adding 0 to these values before searching OR within the first parameter of the VLOOKUP function. EG 'VLOOKUP( C2 + 0, ' or 'VLOOKUP( C2 * 1 , ' etc.  This will turn '009089' into '9089'.

Often people get one VLOOKUP working in a single cell, only to copy and find it only works some of the time. Well, fix your columns or ranges, using $. E.G. 
       =VLOOKUP(A2,Sheet2!$C$1:$F$10,22, TRUE)
So that when copied, the whole matrix searched is unchanged, when copied. This is sometimes called 'Reference Locking'.

The other 'Gotcha' is to define a range and then try to return a column to the left of the defined Table Array range. In this example, a range takes in columns C to F. Column 22 as a return value will never work. Extend the range to the right (ie C to X), in order to pick up the correct values.

Still getting #N/A, but quite obviously you can see a match?  I bet you are trying to locate a number. Have you considered utilising TRUNC( ?? , 0). Excel often will try to be clever and compare 6.7 with 6.700 and decide the values are not the same. Having numbers, that are actually just textual IDs, formatted as Text makes sense.

Excel macro's can also be problematic when adding VLOOKUPs to child links, having child links open first, makes a macro work better.

I hope this helps, you have cost my pension £50 chum, but I don't mind, I am doing my bit for world peace....

Related Post: Are your staff on top of Asset Sustainability & Living Wills? How do you start?

_/_/_/_/_/_/_/_/

You can link with me on LinkedIn here - uk.linkedin.com/pub/tony-smith-bsc-hons-acih/a/979/351/ It would be great to connect !

Prince - You Got The (v)Look(up) .


(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009

PS As usual, if there are subjects you might like me to tackle on this blog, please get in touch and let me know!

File Under: 360,1stTouch,4Js. 07854-655009 ,7854655009, 07854 655009 ," ""07854 655009"" ",07854-655009,#UKHousing,1st Touch,3squared,24Dash,Access Financials,Acutence,Aareon,Academy,ActiveH,Alignment,ALMO,Anite,Apex,ArchHouse,Archouse,asbestos,Asprey e-state pro,Asset Management,Aurora,Average IT Costs,App,Associates,ACL,Abritas,AMS,AX,Aspireview,ACS,Advanced,Agresso,Allpay,Auto assessor,Ablyss CMS,Adastra,Advanced Community,Accuserve,Adobe Echosign,Actionfile,BO,BPR,Browser Applications,Business Objects,Business Process Review,Business social networking, Blockwise, Block wise,BI,BancTec,BluTek,Bluebox,Blueprint,BPM, Castle,CBL,Cedar Open Accounts,Change,Cheaper Housing IT,Chics, CHR,Citrix,Civica,Clearview,CMS , CCS IT Keystone CCSIT, Contractor Systems,CORE,CorVu,Cost Reductions,Covalent,CRM,Crystal Reports,CTI,CTX,Customer Relationship Management,Cashflow, COA,Coactiva,Codeman,Comino,Competitive Dialogue process,complex IT procurements,Component Accounting,Consilium,Consolidation,Consultancy,Consultant,Contact Manager,Context,CIH,Chartered Institute Of Housing,Cadcorp,Cerrus Saturn,convergeOne,CACI,CPL,Cx,Cascade,CSI,Civica Cx,Care and Support,Carenotes,CareSys,CDPSharp,Crosscare,Contract Management,Contact Management,Clinical,Conversions,COINS,Click,Clicksoftware Scheduling,Director,Deeplake,Development Systems,Document Management,Documotive,datasystems ,Dinamiks,DRS,Drupal,ECMK,EDRMS,England, English,EnterpriseBI,ERP Systems,ERP,Elmhurst,Estatecraft,EDM,ESRI,Exponential-e,Epicor,Exel,EOLC,escrow,Finance,Financial Systems,Financials, Factorwise,Facebook ,Fusion,Frontline,Getting best from,Grasp,Grip,GeoSolveIT,GIS,GGP,gotonysmith,Gentoo Streetwise,Guardian24,G-Cloud,Goldcrest,GeoSolveIT,HouSys,Housing software,HG,Housemark survey,Housing Blueprint,Housing Group,hotpixuk, HousingIT,hotpix,Hardware,Hitex,Housing Contact Company,HAMMAR assets repairs forum,HomeHunt,Helpdesk software,HouseOnTheHill,Impact Response,implementations,In House,In4,Infoflow,Information Technology,Informix,inHouse,in-house workforce, Innovation, Inside Housing,Internet Portal,Invu,Ireland,Irish,IT Budget,IT Training,iWorld, Innovation Group Apex,ITIL, In4Systems Promaster,IT,Insight,iCaseWork,iCareHealth,iConnect,iPad,iNurse,InterNETalia,InMotion,InPhase,Joomla,Keylogic,Keystone,Kirona,Kypera,Keyfax,Kofax,Kronos,Ledgers,Linkedin,Locality,Liquid Voice,Lakewood,MD ,Mr Void , MrVoid, Measuring Change,Mebus,Miracle,MISCS, mobile functionality,Monopoly board images and pictures, Montal,MS Dynamics,MS Dynamics CRM2011,Miracle Software,Management Reporting,MI,M3,MIS,MIS-AMS,Mobysoft,Manual,MWL Mavis,Metastorm,Module,Northgate Codeman,Neighbourhoods and Communities, Northgate,Notice,NROSH,NINTEX , NINTEX workflow,Nintexworkflow,NHF,National Housing Federation,National Energy Services,NES,North West Online,NCC,Netcall,OA,OGC Buying Solutions,Ohms,OJEU Limits,OmFax,Omniledger,Open source software,open tender,OpenHousing,Opti-Time,Oracle,Orchard,Outsource and outsourcing,OpenContractor,ORS,OneServe,OpenFinancials,OpenTouch,OSKA,octavia,Odyssey,OpenText, PIMMS Data Systems, Paloma,performance management systems,PfH,Pick,PIMMS,pimms4communities,Planned maintenance,Plus,PM,Progress,Promaster,Proval,Pyramid,PRINCE2,prince,Pamwin,People Value,Peoplevalue,PocketSurvey,Pocket Survey,Pathfinder,Pathfinder RF,Project Management,QL,QLX,Quiss,Qlikview,Registered Providers,Registered Social Landlord,Rent Increase Freezes,Repairfinder,Reporting,Reports,ROCC , Rocket,RPs,RSL,Reality,ReAct,reidmark,RM865,RAM,Real Asset Management,Residata,Rostering,Rootmetrics,Rocket CorVu,Real estate management,Saffron,SAP,Scots, Scottish,Scotland,SDM,sector,Server Virtualisation,servicing, Servitor,Sharepoint,Simdel,Simdell,Slash and Burn,Social Media,Software,SQL Open Housing,SQL Reporting Services,SQL reporting services,SQL Server,Star rating,Stores and Stock,Strategic Asset Management,Sunguard ,Surveys,Sx3,System alignment, Systemwise,SM,Sequoa,Serros,SP,Scout,score,Swordfish,Serengeti,SOTI,south view,Sunguard,Service Charges,Streetwise,Scout Solutions,Strategic Asset Management, Social Housing software & IT-based housing management solutions for social housing providers social housing,management services,software,solutions,social, housing,managed services,managed,business,housing management,solutions , SPOCC,Sprint,Staffplan Roster,Sovereign,SDS,Solarwinds Web Helpdesk,Template,Task,Terminal Services, tonys , tonysm , tonysmi , tonysmith, tonysmithth, tonysmiththa, tonysmiththat, tonysmiththath, tonysmiththatho, tonysmiththathou, tonysmiththathous, tonysmiththathousi, tonysmiththathousin, tonysmiththathousing, tonysmiththathousingg, tonysmiththathousinguy, sharepoint, share point Three Star,Today,Total repairs,Tribal,Twitter,U2, tonysmith , tonysmiththat , thathousing , thathousingIT , thathousingITguy ,TED,Tagish Casework,TP Tracker,Telecetera,Tagra,Trace,TM,TotalMobile,Total Mobile,TPTracker,TapForms,TagTronics,TagtronicsCare,TMWK,UniClass Enterprise,Unidata,Universal Housing,Universalcredit, universal credit, universal credits,UC,Universe,Unrest, UK, United Kingdom ,Ukhousing,UK Housing,U.K. ,Van Stock,Voice and data,Vantage Sentinel,Version One,Visualmetrics,Voluntas,Vanguard John Seddon,Volunteer Module,Volunteers,Vebra,Vixen,VerseOne,Wales,Welsh,Windows Server,Workflow and tasking,Wheatley,Web,housing management solution, Xmbrace,XML ,XenApp,Xen App,Zendesk ,.

Shadowplay

If you do the X / Bluesky thing, follow me at @HousingITguy or LinkedIn here https://uk.linkedin.com/in/tonysmiththathousingitguy

Let’s talk about the dangers of shadow IT in social housing, that awkward family secret everyone knows about but nobody wants written down in a board paper. Only last week, a client asked me in the review I am involved with, “Will you be rooting out the ‘Shadow IT’ around here”? Every landlord has it. Most pretend they don’t. And many are quietly running large chunks of their service on it while telling themselves the “core system” is well under control.

In housing, shadow IT isn’t some hoodie-wearing tech rebel spinning up servers in a cupboard. It’s far more British and far more tragic than that. It’s the Excel spreadsheet that started life as a “temporary tracker” during a HMS system or reputational issue back in 2018 and is now the only place anyone can reliably find your damp and mould cases. It’s the Access database built by someone “who was quite handy with IT” and left three restructures ago (we love restructures folks, don’t we?). It’s the compliance supplier web portal that everyone logs into because, frankly, it works, while the landlord’s own system gets updated later, badly, or not at all.

And before anyone gets sniffy, this stuff doesn’t exist because housing staff are idiots. It exists because systems don’t quite do what was promised, change requests (RFC’s) take forever, reporting is weak, and people are judged on outcomes, not architectural purity. When a neighbourhood officer is told to manage risk, meet deadlines, and keep tenants safe, they will use whatever tool actually lets them do that. Governance doesn’t fix a leaking roof.

The problem starts when the workaround quietly becomes the truth. That spreadsheet stops being “just for now” and becomes the thing everyone checks first. The core system becomes something you update to keep ICT happy. Extracts start doing the rounds by email. Versions multiply. Someone adds another tab. Someone else adds free text. Before you know it, you’ve got personal data, compliance information, and operational decisions sitting in a file called “FINAL_v7_REALLYFINAL.xlsx”. Strangely enough, organisations big & small all suffer from this.

At this point, congratulations. You don’t just have shadow IT, you have mission-critical housing services running outside any meaningful control. No proper security. No real audit trail. Poor or no clear ownership. No resilience when the one person who understands it goes on leave or, heaven forbid, leaves the organisation at the end of their contract. (where’s the knowledge transfer, good question?) 

In a regulated sector, this should be setting off alarm bells. Boards are signing off assurances about data quality, compliance, and risk while large chunks of the evidence live in places the organisation officially doesn’t recognise. Auditors ask for a report and suddenly there’s a very awkward conversation about which system is “the real one”. Spoiler: it’s usually not the one that was procured quickly & cost seven figures.

Excel deserves a special mention here. Excel is brilliant. I use it daily. It is not, and never will be, a housing management system, an asset register, a compliance or any other kind of a database. The moment someone says “don’t worry, we manage that in Excel without really understanding how a VLOOKUP works”, what they really mean is “we hope nothing goes wrong and nobody looks too closely”.

Supplier portals get a free pass far too often as well. Portals are fine if they’re part of a proper data flow. They are not fine when they quietly become the system of record and your own system is playing catch-up. At that point you’ve effectively outsourced control of your data without admitting it, documenting it, or governing it. That’s not innovation or disruption, that’s drift.

The most corrosive impact of shadow ICT is what it does to trust. Once staff learn that “the real numbers” live somewhere else, the core system becomes ceremonial. Data quality drops. Reporting becomes a debate rather than a fact. Digital transformation stalls because nobody wants to give up the thing that actually works, even if it’s held together with formulas a hope and a prayer. I should know, I am a fallen Catholic.

This isn’t an argument for banning spreadsheets or telling staff off. Shadow IT is a symptom. It shows you where systems don’t match reality, where governance has slipped, and where organisations have quietly accepted risk in order to cope.

The danger isn’t that shadow IT exists. The danger is continuing to rely on it while pretending it doesn’t.

Because the moment Excel, a portal, or a side system becomes the place the real work happens, your “core system” isn’t core at all. And everyone in the room knows it, even if nobody wants to say it out loud.

Maybe its time a third party came in and rooted out all your organisations “Shadow IT” chief?

Is your SocialHousing  ICT / transformation project drastically overrunning  ? 

Get in touch , I will drop in for a coffee & bring the KitKats ☕

       Related Post: The right Critical Friend will save you stress, time (and money) ?

_/_/_/_/_/_/_/_/

I would be pleased to connect with you on LinkedIn - http://uk.linkedin.com/in/tonysmiththathousingitguy Message me with any issues or queries, you would like to be explored in this blog. We generally receive a couple of suggestions each month.

Joy Division – Shadowplay

(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009


Access a quick list of our Social Housing ICT blog posts here , what projects do we get involved in? Access them here

Could we help you or your organisation? Our contact details are here , get in touch we will be pleased to chat about your problems and help with your organisation issues.

File Under: #HomesForBritain,#HousingDAY,#InternetOfTenants,#Shout,#UKHousing, 07854655009, 1st Touch,1stTouch,360,365Agile,3squared,4Js, ACL,ALMO,AMS,AX,Aareon,Abritas,Academy,Accuserv,Accuserve,Active Housing,ActiveH,ActiveF,ActiveFinance,ActiveHousing,Acutence,Advanced,Affinity,Aidenn,Agile,Agile365,AHB,AirWatch,Alscient,Alfresco,Alignment,Allpay,Amazon Web Services,Anite,Apex,App,ArchHouse,Archouse,Asprey e-state pro,Asset Management,Associates,Aurora,Average IT Costs,AWI SX Integration Toolkit,AWS, BI,BO,BPR,BancTec,BigChange,Blockwise,BluTek,Bluebox,Blueprint,BrikHousing,Browser Applications,BC, Business Central, Business Objects,Business Planning,Business Process Review,Business social networking, CACI,Capita One Housing,CBL,CCS IT Keystone CCSIT,CEDRM,CHICS,CHR,CIH,CMS,COA,CORE,CPL,CRM,CRM2013,CRS,CTI,CTX,Cadcorp,Capita,CapitaOne,Capita One,Capital Management,Cashflow,Castle,Castleton,Castleton Technology,Cedar Open Accounts,Cerrus,Change,Charges,Chartered Institute Of Housing,Cheaper Housing IT,Chics, Citrix,Civica,Civica CTX,Civica Cx,Civica Genero,Civica Saffron,Clearview,Clik,Cx Assets, Cloud Dialogs,CloudDialogs,Coactiva,Codeman,Comino,Commontime,Community Reward Services,Company,Competitive Dialogue process,Component Accounting,Consilium,Consolidation, Consultancy,Consultant,Consultants,Contact Manager,Context,Contractor Systems,CorVu,Cost Reductions,Covalent,Crystal Reports,Customer Relationship Management,Cx,CxFeedback, DRS,Deeplake,Designer Software,Development Systems,Director,Document,Documotive,Docuware,Dynamic AI,DynamicAI,Dynamics 365,D365,Dynamics365,Dynaway ECMK,EDM,EDRMS,ERP,ESRI,Elmhurst,Enghouse Interactive,England,English,EnterpriseBI,Estatecraft,Esuasive,Esynergy,Etive,Exhibition,Exponential-e, F&O, Facebook,Factorwise,Field Service management,Finance,Financial Systems,Financials,FLS,Footprint,Forms,Freezes,Fusion,Fuzzlab Gas Tag,G-Cloud,GCloud,GDPR,GGP,GIS,GasTag,Genero,GeoSolveIT,Getting best from,GoTonySmith,Grasp,Grip,Group Apex, HCL,HFI (Housing Financials interface),HG,HRA,Hardware,Hitachi Systems,Hitex,HomeMaster,Hometeam,HouSys,House,Housemark,Housemark survey,Housing,Housing Contact,Housing Group,Housing Insight,Housing Management,Housing Management Consultant,Housing Partners,Housing Portfolio Management System,Hub Asset Management,Homeswapper,Housing jigsaw,HousingOne,Housing-One, Housing Support Pro,HousingIT,HousingSupportPro,HyperOptic, IMS,IPC,iProperty Cloud,IT,IT Budget,IT Training,ITIL,Impact Response,In,In4,In4Systems Promaster,InfinityGroup,InfoBoss,InHouse,InMotion,InMotion2015,InMotion2016,InfoBoss,Infoflow,Information,Information Technology,Information@Work components,Informix,Innovation, Informetis,Inphase,Inside Housing,Insight,Internet Portal,Internetalia,Invu,Ireland,Irish,itLab,InclineIT, Keyfax,Keylogic,Keypera,Keystone,Kirona,Kypera,Keynamics, Landlord,Ledgers,Linkedin,Liquid,Locality,Localz, M3,MadeTech,MAVIS,MD,MDM,MI,MIS,MIS-AMS,MISCS,MRI,MS Dynamics,MRI,MS Dynamics CRM2011,Microsoft365,microsoft 365,MWL,Management,Management Server,Manifest,Measuring,Mebus,More IQ,MoreIQ,Microsoft Dynamics 365,Microsoft dynamics GP 2013,Miracle,MobileIron,Mobysoft,Monopoly board images and pictures,Montal,Mr Void,MriEngage,MrVoid,MoreIQ, MRI,MRI/Capita,MRI Housing One, NDL,NINTEX,NINTEX workflow,NPS,NPS ASSIST,NPS Housing,NPS Job Manager Mobile,NROSH,Natural,Neighbourhoods and Communities,Nintexworkflow,Northgate,Northgate Codeman,Northgate Public Services,Notice, OA,OGC Buying Solutions,OJEU Limits,ORS,Ohms,OmFax,Omniledger,One,OneAdvanced,OneServe,Open source,OpenContractor,OpenHousing,Opti-time,Options,Optitime,Oracle,Orchard,Outsource and outsourcing,OutSystems, PIMMS,PIMSS Data,PM,PRINCE2,Paloma,Pamwin,PanConnect,People Value,Peoplevalue,PfH,Pick,PlanForm,Planned maintenance,Plentific,Plus,Portfolio Management System,PowerObjects,Pro,Pro-Points,Prodo,Progress,Promaster,Propeller,Propoints,Proval,Providers,Pyramid, QL,QLX, QLF, QL Yuneo,QuantSpark,Qube,QLX,Qlikview,QueryView,Queryview reporting,Quiss, RM865,ROCC,RPs,RSL,Rave,ReAct,Reality,RedkiteCRM,Red Olive,Registered,Registered Social,Rent Increase,RentSense,RentSenseLite,Repairfinder,Reporting,Reports,RobotAutomation,Rocket,Rubixx, Salesforce,Salesforce.Org,SAP,SASSHA,SDM,SHBVN,SM,SOTI,SP,SQL,SQL Open Housing,SQL Reporting,SQL Server,Saffron,Safron,Saturn,Scenario,Scotland,Scots,Scottish,Scout,Sequoa,Serengeti,Serros,Server,Service,Services,Servitor,Sharepoint,Sim,SimPro, Simdel,Simdell,Slash and Burn,Social Housing ICT,Social Housing Software Applications,Social Media,Software,Software solutions,Spotlight Service,Spotlightservice,Star rating,Stores and Stock,Strategic,SunAccounts,Sunguard,Surveys,Streetwise, Swordfish,Sx3,Symatrix Human,System alignment,Systems,Systemwise, T-Files,TEAMS,TED,TFiles,TP Tracker,TSG,Tagish Casework,Tagra,Task,TAIM,Techlabs,Technologies (India) PVT Limited,Telecetera,Template,Terminal Services,That,Three Star,Tilt,TiltAffinity,Today,Tony Smith,Tony Smith That Housing IT Guy,TonySmith that housing IT,TonySmithHou,TonySmithHousing, ,TonySmithHousingITguy,Total,Total Mobile,Totalmobile,Trace,Tribal,Twitter, U2,UC,UK,UK Housing,Ukhousing,UniClass Enterprise,Unidata,United Kingdom,Universal,Universalcredit,Universe,Unrest, Valueworks,Van Stock,Vantage,Vantage Sentinel,Version,Virtualisation,Visitour,Visualmetrics,Voice,Voice and data,Voicescape,Voluntas, Wales,Welsh,Wheatley,Windows Server,Workflow and,Work Hub,Works Connect, XML,Xen App,XenApp,Xmbrace, Yuneo,TS Acutance Consulting, ...

. . . . . . . . . . . . ES | FR | DE | IT | UK

  

I Owe You One

If you do the Twitter thing, follow me at @HousingITguy
or LinkedIn here https://uk.linkedin.com/in/tonysmiththathousingitguy

A few months ago I focused in this blog, on how good solutions can help better manage repairs delivery. Spookily enough on Twitter, LinkedIn and other places, discussions on why a good proportion of RSLs, have continued to rage. Worthy of much credit is @KwajoHousing – go check him out!

Getting more repairs right first time leads to higher resident satisfaction and better control of what we spend.

In this post I am looking to tackle the other side of the coin. How efficiently socialhousing providers manage effective getting in of the cash that supports all that great asset management and addressing repair issues. Typically everyone monitors percentage of GAD (Gross Annual Debit) collected, but how can systems contribute or not to that blanket figure?

Traditional logic going back to the 1970’s is that arrears are generally chased using an arrears ladder. This is by size and comparison with period charges, rather than based on aged debt, like in most financial situations. If I had £50 for every time I have explained that to finance leaning non-housing staff, I would have probably retired by now and running a successful pedalo business on the Costa Brava by now.

All housing systems tend to come with an arrears and service charge capability, although strangely, not all social housing landlords tend to implement in their core system. Clearly this is the first place to start, even if over time this doesn’t work perhaps long term for the organisation.

Arrears procedures do evolve in time and one major problem is often chunkiness of systems to get a change done, or your supplier looking to charge an arm or a leg to modify. I have come across a few HMS solutions which look flexible when demonstrated, although in practice anything slightly less vanilla, ends up in some bespoke developed SQL SP’s (Stored Procedures). So clearly, not an easy matter to tweak going ahead. With hindsight, the key to having flexibility here is contractually agree that if your supplier creates this bespoke fix, some maintenance charge is paid and they maintain/modify it going forward.

When designing arrears ladders, be sure to consider how automation can progress early stages, without the need for 100% of ‘recommendations’ to be accepted. Where exceptions are known, set a manual status (allowing extra time for a payment to come in, or a bereavement to be managed sensitively etc), instead.

HMS or other suppliers can come across as very unresponsive. RSL’s can clearly get quite hacked off by this understandably. No wonder that other avenues end up being considered.

What avenue is sitting on everyone’s desktop? Yes your friend and mine, Microsoft Excel. Scary but true. Do your staff export accounts and arrears cases to Excel and try to manage from there? Have some of your spreadsheet space cadets built some models and pivot tables, probably that few others actually understand?

It’s a well known fact that most people with Excel on their desktop, were never trained to any level of competency in it. Probably under 5% of Excel users are properly comfortable with formula’s, macros, VLOOKUP (don’t get me started on that one!), or even Pivot tables. Where staff manage arrears in this kind of manner, its often separated from the core system of record. On more than one occasion I have encountered spreadsheets that have been used for years with erroneous formulas and providing incorrect insights for ages.

Another top gotcha, is putting barriers in the way of residents, trying hard to pay their rent. To be clear, my belief is that most people in Social Housing prioritise paying their rent, as keeping a roof over your head is way up there with feeding your family and keeping them warm. When I was a child living with my parents in Council Housing (as was) on the edge of Manchester, that’s what my family prioritised in the mid-1970’s.

My old man was a skilled engineer, one industry already in decline in the 70’s. I recall when he was between jobs, rent was prioritised (fortnightly Gilbert Strip collector expected). Some months we ate a lot of egg & chips as that was all we could afford. Those principles certainly shape my attitude even today to money.

However, our residents no longer see a fortnightly rent collector. Are we providing lots of easy ways to help residents pay? If we offer Direct Debit, how many days can residents sign up for it to be deducted? Do we offer Allpay, or An Post (in the Republic Of Ireland), so residents can easily pay their rent in outlets located in their community?

More important these days, how do we buy Home Insurance, Car Tax and other bills? Well on-line. How easy can your residents pay online? Can they easily pay a period debit, outstanding balance or a bespoke amount? Also, more importantly how fast can they do that from your resident self-service portal? Have a look at your own self-service if it offers payments and count the number of clicks needed to initiate a payment. Is it really friction free?

What I mean in terms of really ‘friction free’, is how close your resident experience of paying rent, in terms of clicks, compared with Amazon. Today I made a repeat purchase on Amazon and it took me just three clicks, to part with £39.99 with Prime free delivery. If your self-service needs re-entry of much info every time, you are making it harder for residents to pay their rent. Challenge your HMS/self-service provider or payments provider, to cut down the number of clicks required.

On the subject of providing multiple MOPs (Methods Of Payments), always be aware of the underlying costs.

Remember too, that most housing systems have the concept of main and sub-accounts. This again is a very under-used (and misunderstood) facility in my experience. Consider how these features can be used, keeping alternative debts away from main rent (possible court action) and considering separate arrears tracking procedures for each. EG Rechargeable Repairs, Court Costs etc.

Lastly, ‘Predictive Analytics’.  This alternative way to target resources, can point effort at accounts that need to be chased, and skip ones where its very likely that a clearance payment will be made, based on past behaviour. Until quite recently, in the UK its fair to say that Mobysoft had close to a virtual monopoly with Rentsense, although many others these days are looking to ‘steal their lunch’ with similar advertised productised offerings. (see MRI/Orchard and others). 

At some point in the next few months, I must try and put together a more detail blogpost on Predictive Analytics, as the landscape has evolved somewhat in the last eighteen months or so.

Predictive arrears is an interesting intervention. Do be sure when considering these, introduce one change at a time and always look into utilising a ‘Control Group’. I am always surprised when I find a client using ‘Predictive Arrears’ that like it, who can rarely tell me that their arrears team, has actually shrunk.

A quick reader question – If you use Predictive Arrears Analysis, have you a smaller, larger or same size arrears team, after using it for 12 months or more? Do leave a comment. I would love to hear any real-world insights from your experiences

Do let me know your feelings via DM or comments 😉

Related Post: Its actually not such a tortuous path to a successful go-live

Catch the blog monthly archive at https://soundcloud.com/housingitguy/

_/_/_/_/_/_/_/_/

I would be pleased to connect with you on LinkedIn - http://uk.linkedin.com/in/tonysmiththathousingitguy Message me with any issues or queries, you would like to be explored in this blog. We generally receive a couple of suggestions each month.

Shalamar - I Owe You One.
(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009


Access a quick list of our Social Housing ICT blog posts here

Could we help you or your organisation? Our contact details are here , get in touch we will be pleased to chat about your problems and help with your organisation issues.

File Under: #HomesForBritain,#HousingDAY,#InternetOfTenants,#Shout,#UKHousing, 07854655009, 1st Touch,1stTouch,360,365Agile,3squared,4Js, ACL,ALMO,AMS,AX,Aareon,Abritas,Academy,Accuserv,Accuserve,Active Housing,ActiveH,ActiveHousing,Acutence,Advanced,Affinity,Agile,Agile365,AirWatch,Alfresco,Alignment,Allpay,Amazon Web Services,Anite,Apex,App,ArchHouse,Archouse,Asprey e-state pro,Asset Management,Associates,Aurora,Average IT Costs,AWI SX Integration Toolkit,AWS, BI,BO,BPR,BancTec,BigChange,Blockwise,BluTek,Bluebox,Blueprint,Browser Applications,Business Objects,Business Planning,Business Process Review,Business social networking, CACI,Capita One Housing,CBL,CCS IT Keystone CCSIT,CEDRM,CHICS,CHR,CIH,CMS,COA,CORE,CPL,CRM,CRM2013,CRS,CTI,CTX,Cadcorp,Capita,CapitaOne,Capita One,Capital Management,Cashflow,Castle,Castleton,Castleton Technology,Cedar Open Accounts,Cerrus,Change,Charges,Chartered Institute Of Housing,Cheaper Housing IT,Chics, Citrix,Civica,Civica CTX,Civica Cx,Civica Genero,Civica Saffron,Clearview,Clik,Cloud Dialogs,CloudDialogs,Coactiva,Codeman,Comino,Commontime,Community Reward Services,Company,Competitive Dialogue process,Component Accounting,Consilium,Consolidation, Consultancy,Consultant,Consultants,Contact Manager,Context,Contractor Systems,CorVu,Cost Reductions,Covalent,Crystal Reports,Customer Relationship Management,Cx, DRS,Deeplake,Designer Software,Development Systems,Director,Document,Documotive,Docuware,Dynamic AI,DynamicAI,Dynamics 365,D365,Dynamics365, ECMK,EDM,EDRMS,ERP,ESRI,Elmhurst,Enghouse Interactive,England,English,EnterpriseBI,Estatecraft,Etive,Exhibition,Exponential-e, Facebook,Factorwise,Field Service management,Finance,Financial Systems,Financials,Footprint,Forms,Freezes,Fusion, Gas Tag,G-Cloud,GCloud,GDPR,GGP,GIS,GasTag,Genero,GeoSolveIT,Getting best from,GoTonySmith,Grasp,Grip,Group Apex, HFI (Housing Financials interface),HG,HRA,Hardware,Hitachi Systems,Hitex,HomeMaster,Hometeam,HouSys,House,Housemark,Housemark survey,Housing,Housing Contact,Housing Group,Housing Insight,Housing Management,Housing Management Consultant,Housing Partners,Housing Portfolio Management System,Hub Asset Management,Homeswapper,Housing jigsaw, Housing Support Pro,HousingIT,HousingSupportPro,HyperOptic, IMS,IT,IT Budget,IT Training,ITIL,Impact Response,In,In4,In4Systems Promaster,InfoBoss,InHouse,InMotion,InMotion2015,InMotion2016,InfoBoss,Infoflow,Information,Information Technology,Information@Work components,Informix,Innovation, Informetis,Inphase,Inside Housing,Insight,Internet Portal,Internetalia,Invu,Ireland,Irish,itLab, Keyfax,Keylogic,Keypera,Keystone,Kirona,Kypera, Landlord,Ledgers,Linkedin,Liquid,Locality,Localz, M3,MAVIS,MD,MDM,MI,MIS,MIS-AMS,MISCS,MRI,MS Dynamics,MRI,MS Dynamics CRM2011,Microsoft365,microsoft 365,MWL,Management,Management Server,Manifest,Measuring,Mebus,Microsoft Dynamics 365,Microsoft dynamics GP 2013,Miracle,MobileIron,Mobysoft,Monopoly board images and pictures,Montal,Mr Void,MriEngage,MrVoid, NDL,NINTEX,NINTEX workflow,NPS,NPS ASSIST,NPS Housing,NPS Job Manager Mobile,NROSH,Natural,Neighbourhoods and Communities,Nintexworkflow,Northgate,Northgate Codeman,Northgate Public Services,Notice, OA,OGC Buying Solutions,OJEU Limits,ORS,Ohms,OmFax,Omniledger,One,OneAdvanced,OneServe,Open source,OpenContractor,OpenHousing,Opti-time,Options,Optitime,Oracle,Orchard,Outsource and outsourcing,OutSystems, PIMMS,PIMSS Data,PM,PRINCE2,Paloma,Pamwin,PanConnect,People Value,Peoplevalue,PfH,Pick,PlanForm,Planned maintenance,Plenfific,Plus,Portfolio Management System,PowerObjects,Pro,Pro-Points,Prodo,Progress,Promaster,Propoints,Proval,Providers,Pyramid, QL,QuantSpark,Qube,QLX,Qlikview,QueryView,Queryview reporting,Quiss, RM865,ROCC,RPs,RSL,Rave,ReAct,Reality,RedkiteCRM,Red Olive,Registered,Registered Social,Rent Increase,RentSense,RentSenseLite,Repairfinder,Reporting,Reports,RobotAutomation,Rocket, Salesforce,Salesforce.Org,SAP,SASSHA,SDM,SHBVN,SM,SOTI,SP,SQL,SQL Open Housing,SQL Reporting,SQL Server,Saffron,Safron,Saturn,Scenario,Scotland,Scots,Scottish,Scout,Sequoa,Serengeti,Serros,Server,Service,Services,Servitor,Sharepoint,Sim,SimPro, Simdel,Simdell,Slash and Burn,Social Housing ICT,Social Housing Software Applications,Social Media,Software,Software solutions,Spotlight Service,Spotlightservice,Star rating,Stores and Stock,Strategic,SunAccounts,Sunguard,Surveys,Streetwise, Swordfish,Sx3,Symatrix Human,System alignment,Systems,Systemwise, T-Files,TEAMS,TED,TFiles,TP Tracker,TSG,Tagish Casework,Tagra,Task,TAIM,Technologies (India) PVT Limited,Telecetera,Template,Terminal Services,That,Three Star,Tilt,TiltAffinity,Today,Tony Smith,Tony Smith That Housing IT Guy,TonySmith that housing IT,TonySmithHou,TonySmithHousing, ,TonySmithHousingITguy,Total,Total Mobile,Totalmobile,Trace,Tribal,Twitter, U2,UC,UK,UK Housing,Ukhousing,UniClass Enterprise,Unidata,United Kingdom,Universal,Universalcredit,Universe,Unrest, Valueworks,Van Stock,Vantage,Vantage Sentinel,Version,Virtualisation,Visualmetrics,Voice,Voice and data,Voluntas, Wales,Welsh,Wheatley,Windows Server,Workflow and,Work Hub,Works Connect, XML,Xen App,XenApp,Xmbrace, ...
. . . . . . . . . . . .