Either Excel or Time Machine or Apple Desktop Broke my Spreadsheets and then fixed one of them

This is scary - where's my data ?

*** The is a descriptive story of an event and situation but does not as of April 2024 contain a solution to the problem described. If I find a solution on how to fix the "file typing" that appears to be broken, I will update this blog entry. If you are having a similar problem, I suggest you get onto Apple or Microsoft support or whoever you think you have the best support relationship.  ***

Background

The storage on modern computers allows us to save many thousands of files adding up to terabytes of data. However, it has been my experience working in both IT support and generally on computers over the years that only a few files are really really important to people and they're often not that big. I certainly fit within that category as two of my most important files are Excel spreadsheets that together weigh just over a megabyte. I've had these two spreadsheets for at least seven or eight years working across a variety of machines. I have both updated the machine I from a Mac powerbook to a new Mac mini to a new Mac studio and I've also updated Microsoft Office when requested from 2007 to the latest versions.

These spreadsheets have lasted the test of time quite well although I have occasionally had to recover them when they became corrupted. Such corruption has not occurred in quite a while and prior to this year the Mac mini and these spreadsheets have been stable for at least 18 months,

On about the 2nd of April I moved from a Mac mini M1 to a Mac studio M2. Using the time machine facility to restore my backups the entire environment move smoothly from the Mac mini to the Mac studio. There seem to be very few glitches or errors, though I did notice that Excel was not was not able to open, file names that it had recorded in its recent history list. Even though according to the file names alongside the entries the files did exist. Double clicking to open files from the Finder worked as expected. The suggested remedy for this was to delete the cash file in ~/library/blah blah blah. 

Technically how's the data stored ?

These files are important to me and they have a couple of layers of password encryption.

Two Excel documents in the format .XLSX.   Once called DoshNSav_piv and the other DoshNsav_Trackers. They are both multi sheet workbooks and have been saved in the Microsoft Excel password format. I have worked on these spreadsheets over many years and they contain pivot tables but no macro code.

Those two Excel files are held within a re-mountable .sparseimage disc image that also has a password. called FinanceDocs.sparseimage .

The disc image is held in the normal Mac file system, it's double clicked when I want to use the files which then mounted as though it was on external media. When finished I unmount the disc image file. This is known as encryption at rest, but it's fairly basic just using built-in utilities as provided by the standard Apple Application Disk Utility.

Data needs to be backed up and as a long time user of Time machine I rely on it for backup services. It's not the only backup I do but these are automatic and regularly saved to an external NAS storage box. Time Machine does a synthetic back up creating a new disc image that contains the entire contents of the drive based on the previous entire contents of the drive. In essence it just saves the data that belongs to new files but retains pointers to the data for older files. When restoring data the backups can be accessed either through a GUI mechanism, where you look back through previous folder listings, or by browsing the historical disk images.  The crucial mechanism is that pulling a file from one of the backups should return the entire contents of that file and its attributes in the way that it was when the backup was run.

Here is a listing from the time machine backups showing each backup with date_timestamp

 

Opening one of the backup images shows the files backed up. The disk image file containing the spreadsheets is listed.

So what went wrong  ?

I decided to open the spreadsheets to do a couple of updates in the same way that I have done over many many years. Spreadsheets would not open giving the error message out the top of this page. This was ugly according to the finder listings they both showed the type Microsoft XL workbook ( .XLSX ) I tried changing the file type (on a copy) to something else and back again but to no avail if anything it just broke the file contents even more. Tried opening them on another machine with a similar configuration, but that also failed with the same error message.

What's in the backups ?

As the contents of the Mac studio were entirely ported over from the previous Mac mini using the Time machine protocol for starting a new Apple machine I thought maybe they will have been some trouble during that conversion. I had a collection of backups of the previous machine as well as backups from this machine. Restoring, the disk images from previous backups worked reliably but when extracting the Excel spreadsheets from the disc image they failed to open. There were two sets of backups  one from the previous Mac mini that ran up to the end of March this year and the other from the new Mac studio. I extracted a set of the disc images from various backups rolling back over time. I extracted the Excel sheets from those disc images to get the listing as follows.

Clicking on each of the Excel spreadsheets quickly showed which ones worked and which ones didn't. It was entirely unclear on first glance as to why some of them would open and some didn't. For some reason some old skills kicked back in and I thought I'd try the UNIX command "file" to see if there was any difference in the file typing. 

clive@BBComp Special measures % file */*                      

2024-0105-225736/DoshNsav_Trackers_piv.xlsx:                                 CDFV2 Encrypted

2024-0105-225736/DoshNsav_pivZ.xlsx:                                         Composite Document File V2 Document, Cannot read short stream

2024-0105-225736/FinanceDocs_2024-0105-225736.sparseimage:                   data

2024-0201-071633/DoshNsav_Trackers_piv.xlsx:                                 CDFV2 Encrypted

2024-0201-071633/DoshNsav_pivZ.xlsx:                                         CDFV2 Encrypted

2024-0201-071633/FinanceDocs_2024-0201.sparseimage:                          data

2024-0303-085151/DoshNsav_Trackers_piv-2024-0303-08515_OK.xlsx:              Microsoft Excel 2007+

2024-0303-085151/DoshNsav_Trackers_piv.xlsx:                                 CDFV2 Encrypted

2024-0303-085151/DoshNsav_pivZ.xlsx:                                         CDFV2 Encrypted

2024-0303-085151/FinanceDocs_2024-0303-08515.sparseimage:                    data

2024-0314-153641/DoshNsav_Trackers_piv.xlsx:                                 CDFV2 Encrypted

2024-0314-153641/DoshNsav_pivZ.xlsx:                                         Apple Desktop Services Store

2024-0314-153641/FinanceDocs-2024-0314-153641.sparseimage:                   data

2024-0320-065313/DoshNsav_Trackers_piv.xlsx:                                 CDFV2 Encrypted

2024-0320-065313/DoshNsav_pivZ.xlsx:                                         Apple Desktop Services Store

2024-0320-065313/FinanceDocs_2024-0320-065313.sparseimage:                   data

2024-0324-011945/DoshNsav_Trackers_piv.xlsx:                                 Apple Desktop Services Store

2024-0324-011945/DoshNsav_pivZ.xlsx:                                         Apple Desktop Services Store

2024-0324-011945/FinanceDocs_2024-0324-011945.sparseimage:                   data

2024-0327-134121/DoshNsav_Trackers_piv.xlsx:                                 Apple Desktop Services Store

2024-0327-134121/DoshNsav_pivZ.xlsx:                                         CDFV2 Encrypted

2024-0327-134121/FinanceDocs-2024-0327-134121.sparseimage:                   data

2024-0331-214023/DoshNsav_Trackers_piv.xlsx:                                 Apple Desktop Services Store

2024-0331-214023/DoshNsav_pivZ.xlsx:                                         CDFV2 Encrypted

2024-0331-214023/FinanceDocs_2024-0331-214023.sparseimage:                   data

2024-0401-211118_MMlast/DoshNsav_Trackers_piv.xlsx:                          Apple Desktop Services Store

2024-0401-211118_MMlast/DoshNsav_pivZ.xlsx:                                  CDFV2 Encrypted

2024-0401-211118_MMlast/FinanceDocs-2024-0401-211118.sparseimage:            data

2024-0402-082024_BBCFirst/DoshNsav_Trackers_piv.xlsx:                        Apple Desktop Services Store

2024-0402-082024_BBCFirst/DoshNsav_pivZ.xlsx:                                CDFV2 Encrypted

2024-0402-082024_BBCFirst/FinanceDocs_2404-0402-082024_BBCFIRST.sparseimage: data

2024-0415-000021/DoshNsav_Trackers_piv.xlsx:                                 Apple Desktop Services Store

2024-0415-000021/DoshNsav_pivZ.xlsx:                                         CDFV2 Encrypted

2024-0415-000021/FinanceDocs_2024-0415-000021.sparseimage:                   data

These results were very useful. it turns out where the file is of the type "CDFV2 Encrypted " the file would open correctly and where it is marked with "Apple desktop services store" quotes opening gives the failure. During the entire historical usage of the spreadsheet I have never knowingly changed the type of the spreadsheet. It had always been an encrypted, saved with the password Excel spreadsheet. 

What is very peculiar and highlighted above how the files change types and then back again. See lines marked in yellow and red.  In the yellow lines, the file DoshNsav_pivz changes type between backups and then in the red lines, back again. Meanwhile the _Trackers file changes to Apple Desktop Services but not back again and remains inoperable.  The file with type "Microsoft Excel 2007+" is a version saved without a password.

It would seem that somehow the Excel files have lost the marker that indicates they are encrypted. Excel then tries to open them as a plane file and obviously fails on some kind of structural integrity test. I cannot think of any explanation as to why this marker would change and or change back during the process of doing a backup. This is a data integrity issue as files and their associated attributes should not be changed through the process of doing a backup and restore. Thee may have been macOS or Microsoft update in that time span.

Who needs to fix this ?

That's an interesting question and during my career in tech support I have been caught between vendors each of which say the problem belongs to the other one. For me there needs to be some kind of mechanism to see and or change the type category of the file to ensure that Excel will open them as an encrypted/password file if they are an encrypted/password file. I understand that Microsoft has used a number of different encryption schemes for their spreadsheets. Somehow coordination has been lost with the file type handling in time machine.

Personally I would would love to help but I bet if I phoned up either tech support team they would say please send over your files and the passwords used and be honest because this is personal financial information that's not gonna happen. The other way is to re-create the problem, but I'm sure that would take quite a while to find the edge condition in historical backups that is causing this problem.

If you think you see this same problem feel free to use the file command trick in terminal to identify which files can be opened or not and if you see the same symptoms as above put a support case in with either Apple or Microsoft whoever you feel will give you the best response. Let me know in the comments below if either you've seen this problem or if you have a fix or more information about it.

In Summary

• This is a strange one. It appears the attributes of the Excel file have been lost / changed or misinterpreted.
• It's very hard to spot when this has occurred as there is no external visible marker (except the unix file type) as if an Excel is in password protected format or not.
• Some file types are identified by the dot3 or four letters on the end others are identified by data markers within the file. Excel appears to use a combination of both to identify whether a file has a password encryption or not.
• AAAARGH lucky I have backups and know how to use them.

.

And the house top computer performance badge is passed

The house badge of top computer performance passed to a new machine today.

Back in 2007 I put together a PC that would match the performance of a T90 Cray supercomputer. It needed to be able to do more than 30 giga flops and have over 20GB of memory. Turned out to be a great machine with a water cooled 12 processor CPU,  3 * 9TB Hdd for lots of storage and a Nvidia graphics card for evaluating the Cuda programming environment.

The previous hand built machine was used it for Folding at home, Cuda programming research, some gaming and other computational heavy lifting. ( more notes here ). 

 

Now upgraded to ....

The new machine is a M2 Studio with 2TB storage, 96GB memory and now proudly wears the house top computer badge.

   

Using a simple single thread benchmark the Studio is about 40 * faster than the previous machine taking just 9s to deliver results that the older machine chewed on for over 6 minutes. The studio is also silent and draws very little power.  The computational test is ...

% time echo "6^7^8" | bc -l | cksum

2486905450 4617825

The top spec Studio

It's not just the computational performance that makes this machine outstanding. Disc speed to the internal SSD measures at a whooping 6.5 GB a second compared with 300 MB a second to a directly attached SSD and 100 MB a second over gigabit to a NAS box of storage. As the Mac Studio has an absurdly large amount of ram, it's probably buffering the internal IO that achieving the phenomenal speeds.

Mac Studio

USB 3 Attached SSD

Network atached storage

Puzzle solving entries have moved to a new blog.

The puzzle solving entries of this blog have moved to https://puzzlesolvingsmtstyle.blogspot.com/

Check out how to reframe common puzzles into the language of an SMT solver.

For example:

• 

  Solving Classic and other Sudoku using an SMT (Integer) solver.

  Sudoku is a popular recreational puzzle comprising a 9 by 9 grid of numbers all in the range 1..9. The puzzle must be completed whilst obser...
• 

  Solving Kurosu using and SMT (Integer) solver

  Solving Kurosu using an SMT (integer) solver See Original and updates on https://gannett-hscp.blogspot.com   ** UPDATED  May 2023 fix method...
• 

  Chess Queens social distancing problem using an SMT (integer) and boolean solvers

  Placing chess queens on a grid such that social distancing is maintained is a classic problem. We look here at a method for solving this pop...

  
  

  
  

 

Digital security for your classic or performance car

Digital security for your car and/or motorbike

We all love our classic and performance cars and would be heartbroken if it was lost or stolen. As well as the standard arrangements of immobilisers and car alarms that can be tricky to fit to classic wiring there are a couple of new techniques for protecting your classic car or motorbike. Some initial technology is required as both these techniques work in combination with an Android or Apple iPhone.

Active CCTV

CCTV for your house and garage has come a long way in the last 10 years. The cameras are much more intelligent about recognising when cars or people move within the area of view. If you have house fed power to your garage, you can almost certainly fit CCTV cameras that will constantly monitor the garage inside and out. Cameras can be configured to alert your phone when there is a movement within your garage. Unlike traditional alarm systems an ongoing subscription is not required as monitoring and alerting is done directly to your mobile phone. 

For garages beyond the reach of power and Wi-Fi self sufficient cameras are available that will  send the pictures using a mobile phone signal. Some charges would apply in this case. Separate boxes for recording are also redundant as cameras can store recordings of interest on an included memory chip. The modern camera types are no longer just a passive recording or scanning devices and are available with two way audio and siren trigger. Typically, the price for a powered network enabled camera is about £70.

Reolink battery powered camera with two way audio and siren trigger

Tracking Tags

The second most interesting development to keep your car safe and located is the small tracking AirTags. A small tag about the size of a £2 coin is secreted somewhere on the car. When the car moves beyond defined limits alerts are sent to your phone. The tags provide occasional updates as to the location of the car that can then be tracked and located. These AirTags have a replaceable battery that lasts about a year. The tags work by using the same technology that is used to find lost phones. Whilst they do not have specific GPS receivers, they send their location back to base using any available phone that comes into the range. As there are over 1 billion mobile phones on the planet these days you're never that far away from a mobile phone. 

These tags are great for knowing the last known time and location of a car. There are a couple of little wrinkles when using air tags that are separated from the phone for longer periods of time, however, this type of device can provide an extra layer of security and a recovery for a classic car. The cost for an apple AirTag is about £35 each or £120 for a four pack with no on going subscription apart from what's needed to keep your mobile phone going.  

The dangers of thieves and stalkers planting these tracking tags has been mitigated within the “Find My” tracking system but everyone should be aware of their existence.  

Apple AirTag, about 1 inch / 25mm in Diameter

  

Other more active trackers with live GPS locations are available but come with an ongoing service subscription cost. 

SuperToy waits for the summer driving fun to begin.

Cray Research Supercomputer mini videos and other exhibits

During a visit to The Computer Museum of America TCMoA  some time was spent generating mini videos of the Cray Research supercomputers on display. Check out the collection here.

A couple of other exhibits from the Cray-History.net website follow....

Cray Research Supercomputer family tree

Cray 2 on display at Muse Bolo

Cray XMP on display at Muse Bolo

Cray 1 Module location map

Cray YMP-EL internal views

Excel rounding feature

** Update 2023 Added When Excel goes ( is used badly ) impacting careers 

I have an Excel spreadsheet that I use for keeping the accounts for a small club. The spreadsheet has one page for income, one page for expenditure and another page that totals up and displays current balances. On the income and expenditure page I add an entry for each transaction. The line starts with a total amount for that transaction and then the same amount spread across various columns depending on which items that transaction relates to. I then sum the columns and they should cross match. The sheet uses simple conditional colouring red and green to indicate when numbers that should add up don't add up. 

I was just about to send a spreadsheet to a new colleague when I noticed one of the red indicators showing and yet despite the numbers appearing to add up correctly. After extracting the relevant numbers into a fresh new spreadsheet, I could see that the difference between the two sums was very very small, a rounding error of  0.0000000000109139 

Excel has a feature where you can set the display precision of numbers typically used for currency, that its two decimal places. However, internally it will work to much higher position unless a certain option is checked for the spreadsheet. That option "set precision as displayed" is supposed to ensure that Excel uses the same precision for calculations and display. This is generally a dangerous option as you can get accumulations of errors through lack of precision in complex sheets. 

On the sheet extract shown below the indicator square has the formula

=IF(SUM(B8:B397)=SUM(E4:T4),"MATCH","FAIL")

The vertical numbers are summed into square C6, and the horizontal numbers summed into D6. Subtracting C6 from D6, gives us the size of the rounding error in square D8. 

Changing the Excel option for  "set precision as display" changes (and clicking on the warning) the value of the displayed rounding error shown in D8 to 0 as one would expect. However, the indicator square remains at FAIL showing that the interior precision is maintained during the above type of test operations.

The fix was to change the indicator to use ABS(C6-D6)> 0.000001 as the test for a match.

On a Mac the option can be found at Excel -> Preferences -> Calculations

Using Excel for Mac 16.67.

Apple Powerbook 230 - resurrection and other old Apple items

Okay I admit it in a moment of late night madness I bought an old MacBook Duo 230 on eBay. The usual late night buying rules applied I didn't look close enough at the listing and the listing skated over a few fairly essential details. The reason for the purchase, obscured by the late night enthusiasm, was to resurrect some data as part of a computer history investigation project.

I was super optimistic when the package arrived because the laptop floppy disk drive power supply and the essential Duo mini dock all arrived packed with bubble wrap. Obviously sent by somebody who cared for the safe delivery of the system.

Once powered up the system booted and sprung into life. The screen was looking a little fuzzy with artefact lines running out from some corners but was plenty readable and as a bonus the mouse ball worked as well. The hard drive was a little nosy but the main issue was system software, or lack of it, on the machine. The only icons visible on the screen were the disc tools rescue set. This is a small collection of utilities, used to check hard and format disk drives, was just enough boot the machine but provided no applications. 

One more eBay purchase, from ebay seller applefloppiesonline, later and I had a set of floppy discs, MacOS system 7.5, suitable for this machine. 

I had forgotten how long it took to do installs from floppy disks, typically about three or four minutes per floppy disk. If an install fails then the process backs out and you're basically exactly where you started. This happened a couple of times. Was fun for a while to listen to the whirr and click of the floppy drive but playing games of install "Snakes and Ladders" was not really what I was after. 

The floppy disk install eventually worked but I had a lot of trouble reading Install Disk 4. See videos below.

With the system booted off the install disk 1 and reading all the other disks with no trouble, This install a couple of times on Disk 4. This repeated failure issue by trying a few OS installer tricks remembered from back in the day:

• Copying Install disk 4 contents on to the hard drive then back out on to a spare Floppy drive. 
• Doing a custom minimal install for the this Mac only. ID4 is needed by all the system install options as it has the control panels files but this choice would minimise the files loaded from ID4.
• Renaming the system folder. Ater a crash and reboot this didn't seem to make any difference as the install still used that folder. I speculate that the rename action may have cleared some read-only items in the system folder.

Not sure which of the above fixed the issue but we got there in the end. Now the system is up and installed with the utilities and software that it needs I can see that the on-board mini batteries that preserves the date and time between connections has failed which is not unusual for older machines. Also failed is the main battery neither charges nor holds a charge. This limits the machine to running on plugged in power and necessitates resetting the date and time from 2039 back to the current date each time the machine is unplugged.

Ports from Left to right
Built in modem hole ( missing ), Kenington lock, Mic, speaker, Scsi disk, Printer,  External modem , VDU screen, AppleTalk networking, Floppy Disk, Power, on/off switch.

Despite the many connectors on the back of the dual dock there are few that are in used today. Apple desktop bus, for connecting keyboards and mice, has been replaced by the universal serial bus ( USB ). modern screen connections are now VGA or DVI replacing the 25 way pin socket. Most importantly networking has moved on from AppleTalk wiring to CAT5 ethernet.

I guess if I was going to do this again I would consider more carefully in the machine I was going to buy. I probably would've gone for a blue and white G3 as that machine was on the cusp of changing technologies and could use both the older keyboards mice as well as USB. Had Apple talk as well as ethernet. We had that very machine with a USB floppy disk drive that went into the more modern Macs and that would've probably been an easier choice for moving data from old Apple floppy disks to a modern / internet connected system.

What's next for the Duo. The data rescue project has moved on and without a modern network connection this may not be the older machine I am after. Tinkering around with the old technology was kind of fun but without more relevant application software it's hard to show the capabilities of this older machine.

See on YouTube here. Install Fail 1 and Install fail 2

A few photos from the failing installs. 

Finally installed and showing the Memory Control Panel.

The machine I probably should have bought for this project.

Along with one of these ... USB Floppy disks

Just for old times sake the Apple machine we loved the most - The sun flower G4 iMac. This was the second version with the wider flatscreen.

For further reading on old time Apple kit see