Excel and SVG

Topic

New Reply

The background isn’t strictly necessary for the question, but it might entertain.

Charts in financial markets are a mess: $ or € or £ or ¥ might be represented by different symbols on different charts. Indeed, sometimes different symbols in charts created by the same author and appearing on the same page. A consistent set of markers would allow readers to focus more on the meaning of the data, and less on the distraction of attempting to decode the markers. Such a set of 123 markers has been made, and will be open-sourced under the Boost licence.

These have been coded concisely into SVG. (Concisely: currently smallest is 157 bytes; median is 263 bytes; most verbose is 824 bytes.) Used as chart markers in Excel, typical size is to be about 10×10 pixels. Each SVG begins with something of the form < svg width='8.54737' height='11.6'…>, the extra space being required to beat this BB’s defences. (Height rounded to reduce bytes; width computed from rounded height, and not rounded, so that aspect ratio is correct.)

They are imported into mac Excel 16.43 via a loop containing VBA resembling

Set sh = .Shapes.AddPicture(FileName:=…, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=…)
sh.LockAspectRatio = msoTrue

Alas, Shapes.AddPicture seems to round the size of the picture to ½pt, which, for small things, can mess with the aspect ratios. I want my aspect ratios good, not wrong by ≈ ±½/10 ≈ ±5%.

Second attempt, is to specify Width:=200, Height=-1. Alas, that destroys the aspect ratio, which becomes Ambassadors-style super-wide.

Third attempt is regenerates the SVGs, much wider: < svg width='854.737' height='1160'…>, and import those. Aspect ratio deliciously accurate: hurray. But Excel attaches a PNG copy of the SVG, averaging about a million pixels, times 123 markers, is a file of 26 megabytes, which seems heavy penalty for the inclusion of 43k of SVG.

I’m stuck. Please, how Excel be persuaded to:
• Import a picture without rounding picture sizes.
• Import a picture without attaching a PNG copy?

Thank you.

Reply | Quote

Replies

Julian, a couple of things you could try towards you’re second dot point/option…

Firstly, try AddPicture2 rather than AddPicture. It has a ‘compress’ option which you can set to ‘msoPictureCompressTrue’.

Secondly, and not ideal from many aspects, I’m assuming you’re using a modern excel file version (e.g. xlsx) in which case after the file is created you can open the file in your favourite zip editor (e.g. 7zip), navigate to xl\media then delete all the png files. In my testing this reduced the file size and didn’t kick up any errors when I re-opened the file.

If I come up with any other ideas I’ll post back here and I’d be interested in your feedback on how either of these options worked for you.

1 user thanked author for this post.

Julian D A Wiseman

Reply | Quote

btbs wrote:

Julian, a couple of things you could try towards you’re second dot point/option…

Firstly, try AddPicture2 rather than AddPicture. It has a ‘compress’ option which you can set to ‘msoPictureCompressTrue’.

Secondly, and not ideal from many aspects, I’m assuming you’re using a modern excel file version (e.g. xlsx) in which case after the file is created you can open the file in your favourite zip editor (e.g. 7zip), navigate to xl\media then delete all the png files. In my testing this reduced the file size and didn’t kick up any errors when I re-opened the file.

If I come up with any other ideas I’ll post back here and I’d be interested in your feedback on how either of these options worked for you.

I did not know about Shapes.AddPicture2: thank you. But, alas, compress:=msoPictureCompressTrue does not shrink the file size. Perhaps it is compressing my (concise) SVGs, but not compressing the PNGs it makes.

Next up, I need to choose and install a zip editor.

Reply | Quote

BetterZip was used to remove the PNGs, each about 250k. Excel, on opening, expressed unhappiness about file damage.

> We found a problem with some content in ‘[filename]’. Do you want us to try to recover as much as we can? If you trust the source f this workbook, click Yes.

The error being (with path edited):

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to ChartMarkers0.xml</logFileName><summary>Errors were detected in file ’[path deleted for posting].xlsm’</summary><additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Drawing from /xl/drawings/drawing2.xml part (Drawing shape)</repairedRecord></repairedRecords></recoveryLog>

And the SVGs are shown only as their bounding box. Maybe Excel can’t paint SVGs. Sigh.

Reply | Quote

Julian, are you able to zip and share an example file for us to experiment with?

Reply | Quote

http://www.jdawiseman.com/2022/ChartMarkers_few.xlsm

http://www.jdawiseman.com/2022/TenMarkers.zip

Reply | Quote

Julian,

I imported your ten markers into an excel file ‘TenMarkers Test orig.xlsx’ which saved at 108k size. I copied the file to ‘TenMarkers Test no png.xlsx’, used 7zip to ‘open archive’, navigated to xl\media folder, deleted all png files then closed/saved the file – this new file saved as size 16.8k.

When I opened ‘TenMarkers Test no png.xlsx’ excel didn’t report any errors and seemed to be fine.

You can get 7zip here: https://www.7-zip.org/download.html.

[EDIT] In you later post you said you’d compressed the png files. Don’t compress the png files, delete them and see how that goes before trying 7zip.

I know it’s still a work around to your issue but best I can do at the moment.

1 user thanked author for this post.

Julian D A Wiseman

Reply | Quote

1. In TenMarkers-Test-orig.xlsx they are not correctly imported. The JPY marker should be a circle in a circle, but appears to be wider than high. Likewise the others, most obviously USD and NZD, seem to be stretched wider. 🙁

2. Opening the TenMarkers-Test-no-png.xlsx doesn’t work for me (Excel 16.43, macOS 10.13.6). Screengrab at
http://www.jdawiseman.com/2022/TenMarkers-Test-no-png_screengrab.png

3. 7-zip seems to be Windows, and I’m on a Mac. But I will test other programs. But the previous image isn’t optimistic.

Reply | Quote

I didn’t try to scale them or anything, just did a simple import to see if I could reduce the size of the file. Maybe it’s a windows vs mac issue that the ‘edit – delete’ process works for me but not you.

1 user thanked author for this post.

Julian D A Wiseman

Reply | Quote

It does seem that Mac Excel needs the PNG, but Windows Excel doesn’t. You’ve been helpful, thank you, but I need to play some more.

Reply | Quote

Compressing the PNGs from BetterZip, via open with ImageOptim. On re-opening the xlsx Excel again grumbles, but saving from Excel seems to keep much of the PNG compression. And on reopening Excel is grumble-free. So this is progress: thank you.

Reply | Quote

I use currency symbols a lot in similar circumstances and simply use the ones in the standard fonts  eg

¥ £ $ €

Forgive me if I have misunderstood, but couldn’t you do the same ?

Reply | Quote

WSMartinM wrote:

I use currency symbols a lot in similar circumstances and simply use the ones in the standard fonts  eg

¥ £ $ €

Forgive me if I have misunderstood, but couldn’t you do the same ?

Consider a chart of eurozone government bonds: x = maturity or something better; y = asset swap (loosely speaking, ≈ the credit risk priced by the market). Relevant debt issuers, one chart series each, could include ① the EU itself, ② the ECB, ③ Germany, ④ France, ⑤ Italy, ⑥ Spain, ⑦ Netherlands, ⑧ Belgium, ⑨ Austria, ⑩ Finland, ⑪ Greece, ⑫ Portugal, ⑬ Ireland, ⑭ Slovakia, ⑮ the ESM, ⑯ the EIB, and ⑰ the EBRD. I want each to have a consistent marker, easily remembered because echoing something about the issuer.

One might suggest using letters (EU ECB DE FR IT ES NL BE AT FI GR PT IE SK ESM EIB EBRD). That can work for a few datapoints, but anything works for only a few datapoints. When there are many then letters become a black-and-white Rorschach jumble. A graphic design, colour and shape, is much easier for the eye to perceive.

That is what this project is.

I think my designs and SVG craftsmanship are beautiful, and when open-sourced—soon!—you can judge for yourself. Naturally, I’d like to minimise the extent to which Excel butchers the beauty. Hence this question.

Reply | Quote