Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Thursday, 30 January 2025

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.

Wednesday, 25 May 2022

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?

Wednesday, 16 June 2021

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.

Tuesday, 23 June 2020

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



Thursday, 16 February 2017

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.

Friday, 23 September 2016

Windows 10 Anniversary Update (1607) and problems with Excel

Some Microsoft Windows 10 users have recently been reporting that their Microsoft Excel either crashes or hangs regularly, particularly after the Windows 10 Anniversary Update to version 1607 in August 2016.

The issue can often be traced to printer driver incompatibilities with Windows 10 which affect the correct operation of Excel. Correct operation of Excel can usually be restored by temporarily setting your "Default" printer driver to either "Microsoft Print to PDF" or "Microsoft XPS Document Writer" until you can obtain a Windows 10 compatible printer driver from your printer manufacturer.


HP Laserjet users can find the "Recommended Solution" for their printer model at http://h20564.www2.hp.com/hpsc/doc/public/display?docId=emr_na-c04675396.