Sage 50 SDO field names not necessarily the same as the ODBC names

A heads up, when working with Sage Data Objects, the field names are not necessarily the same as those recognised in ODBC queries. For example, the AUDIT_SPLIT table has a field called EXTRA_REF when using the ODBC driver which is called INTERNAL_REF in Sage Data Objects. Check out the SDO help file documentation if the expected field name (the ODBC name) doesn't work.

PV bloopers by the "professionals" - Part 2

Having installed an Economy 7 (2 import and one export tariff) meter I arranged for an additional 3.6kWh Dyness battery to be added to my existing two battery array. Here's the day of the install:

The irregular State Of Charge % trace bothered me but I thought it might take some time for the batteries to "balance". Anyway, I got up in the middle of the night when the inverter was due to draw cheap electricity from the grid and this is what I found:

At around 1AM, when the inverter started its battery charging cycle, the load changed from around 0.2kW to around 1.4kW.  Something was very wrong. After researching the configuration of the battery stack, I discovered that the installer had configured them as 2 Master units and 1 Slave unit instead of 1 Master and 2 Slave units. This I fixed.

I dread to think of the consequences if this error had gone unchecked. Could the inverter or batteries have been irreparably damaged? Would there have been a significant financial penalty? I've resolved to always check what the "professionals" have done to the best of my ability. 0 for 2 (in American parlance) isn't very reassuring.

PV bloopers by the "professionals" - Part 1

I decided my solar panel setup could do with the help of an additional battery together with changing my tariff to Economy 7 (cheap nighttime electricity). I'd been drawing from the grid at night for a short time to get a feel for how it would all work. A meter change was required and an appointment was booked for the installation. 

Some time around 6 PM on the day of installation I went to my SolisCloud app. To my horror this is what I found:

Massive movements, something wasn't right. When I checked the meter I found that a clamp on one of the leads that reports import/export volumes to the system had been replaced the wrong way round! Put the clamp back the way it should be and, voila, back to normal:

Very disappointing. I dread to think of the consequences if I hadn't spotted the issue fairly quickly.

Microsoft Office UK/US language confusion

I've been having a nightmare with dates and spelling showing as US format. Here are my Windows settings:

and, here are my Office settings:

I wondered which sort of English that might be and so clicked on the “Add a Language” button to add “English (United Kingdom)”. This resulted in a changed display where “English” was replaced by “English (United Kingdom)”. 

I think most systems will be set to “Match Microsoft Windows [English]”. In spite of saying it matches Microsoft Windows, it doesn’t actually match the UK bit of the Windows setting and, instead, uses US English! 

Now that it reads “Match Microsoft Windows [English (United Kingdom)]”, I'm getting what I want. How confusing is that?

Excel High CPU Usage

My laptop sounded like it was working hard so I opened up Task Manager in Windows to see what was going on and, to my surprise, I found that Excel was reporting greater than 50% CPU usage. Permanently it would seem. After a lot of blind alleys I eventually discovered that the zoom setting on the tabs seemed to be the critical factor. At 100% zoom on all 12 tabs the CPU problem persisted. Setting them all to 90% zoom seemed to make the problem marginally worse (approaching 60% CPU usage). Setting them all to 80% zoom seemed to make the problem go away. 

Searching for what might be the issue, I noted that freeze panes was active on one of the sheets such that the scrolling part of the screen was only just visible at 80% zoom. Trial and error determined that the scrolling part of the screen disappeared at 82% zoom and that was the exact zoom level that the CPU usage problem occurred. I removed freeze panes from the sheet and set the ScrollArea in worksheet properties to achieve the same sort of "locked area" effect that freeze panes was used to create and had a usable workbook again.

Trouble with Time (Text ODBC driver)

I have a text file with Date and Time fields containing the values 01/01/2020 and, for the time field, 13:00:22

In Microsoft Query the values show as 2020-01-01 00:00:00 and 1899-12-30 13:00:22

When returned from MS Query to appropriately formatted columns in a Query Table they show as 01/01/2020 and 00:00:00

However, if I create a DateTime field as Date+Time it shows as 01/01/2020 13:00:22

How to get the Time to show correctly in the Query Table?

Time + 2 as [Time]

This shows as 13:00:22 in the Query Table. Took me ages to figure this out. I spent ages playing around with the DateTimeFormat option in the Schema.ini to no avail. It seems that if MS Query returns anything prior to 01/01/1900 00:00:00 the time portion will always show as 00:00:00 in Excel

Excel 2016 version 1612 change

Following the release of the Microsoft Office 365 update to Excel 2016 version 1612 (January 25, 2017), there has been a change in Excel’s default behaviour in relation to PivotTable subtotals when a new field is created following a Group operation. Until now, the new field has been created with the subtotals set to “None”. Now, the new field will be created with the subtotals set to “Automatic”.

I haven’t checked whether the same change occurs with subscription versions of Excel 2013.