Advertisement

Blog The Filter Wizard Remastered

Excel Tunes Up your Schematic Files

In previous posts I’ve talked about transferring spreadsheet-based circuit design “directly” to the lovely LTspice simulator, and a reader of The Filter Wizard was keen to understand exactly what “directly” actually meant, and to get some practical information on how to do it.

The approach takes advantage of the ASCII formatting used by LTspice for its .asc schematic files. My method is nothing more sophisticated than to import this text file into Excel, use standard spreadsheet formulae to manipulate its content, and then write it back out again. But I’m sure you’ll find a worked example to be useful, so here goes!

You start with an LTspice schematic, so here’s a simple one in Figure 1. Just a single RC network fed by a voltage source, with boring starting values for the components. You don’t even need to put values in for this method to work, but I recommend that you do. That way, you can analyze the circuit and make sure that it basically does what you want without falling over. This approach is definitely useful for more complicated circuits than this!

Figure 1

A simple generic circuit to start with.

 

A simple generic circuit to start with.

 

Draw this schematic up in LTspice and save it as a schematic file. Then open it in a text editor – right-click the file and chose ‘Open with…’ as the option. On a Windows system you can use NotePad as the .asc files are pretty compact. WordPad works too., but might let you down later. With my example, you’ll see the following text:

If you’ve reproduced my circuit, you may see different numbers in the statements; they are mostly the (x,y) coordinates of drawing points, and aren’t important here.

Now, select it all and copy it to the clipboard. Then open Excel (any other spreadsheet program will almost certainly work just as well). Click in cell A1 of your chosen worksheet and paste; you’ll get the lines from the .asc file pasted into contiguous cells in column A, as shown in Figure 2.

Figure 2

The .asc file imported into Excel.

 

The .asc file imported into Excel.

 

Now, I usually give this a worksheet of its own within the Excel file; it’s typically a file that I’ve already been doing some filter design in. But for this example we’ll just do some simple manipulations based on some calculations we can do right in the spare space on the starting sheet.

Let’s change our impedance level to 1 kohm and push the cutoff frequency to 2121 Hz (any phono preamp fans out there? You’ll recognize that number.). We’ll also calculate some suitable plot limits for the analysis, That’s done in the central regions of Figure 3, which shows how my sample worksheet is going to end up; you can probably reverse-engineer the calculations for yourself.

Now we do the substitution bit, it’s easy. Some of the rows in column A are invocations of LTspice syntax than cause a value to be associated with a component.

The two lines on rows 22 and 23 of Figure 2 set up an instance of a capacitor and set its value to 1. To couple the value invocation into the spreadsheet, we change this into a formula. We use the string concatenation operator & to assemble a cell that contains the original syntax as a string, and the desired value as a numeric picked up from the spreadsheet

Figure 3

Click here for larger image Change cell contents from text into formulae that reference values.

 

Change cell contents from text into formulae that reference values.

 

In this case we change

in cell A23 to

because G23 is where the 75 nF-ish capacitor value we need has been calculated. When you view the content of the cell it says

and when we copy out column A, that’s what we’ll get, plus whatever changes we make in other affected cells. So in Figure 3 I’ve made the changes I need to, and also shown comments in column E describing what I did. Note that you can do any formula stuff, not just referring to other cell contents, so you can put entire expressions in here.

The final step is to select column A and copy it to the clipboard in the normal way. Now, open your text editor again, and paste in from the clipboard. Here, by the way, WordPad won’t do, because it has some comprehension of Microsoft clipboard formats and will give you a table , not a text file. I use NotePad, which works fine. This should give you a text file that reads like the content of column A in the spreadsheet. Save this as a suitably named file with an .asc extension. Now open it with LTspice and, hey presto, you should see the circuit shown in Figure 4:

Figure 4

Open the text file with LTspice

 

Open the text file with LTspice

 

Figure 5

Plot magnitude (green) and phase (pink).

 

Plot magnitude (green) and phase (pink).

 

When you click the little running man (LTspice users will know what I mean) you’ll get a response plot very much like Figure 5. Well, it’s unlikely to have a chain-dotted pink phase trace, but I’ll get to that in the next section. Anyway, this shows a simple example of this technique, but it can be generalized to do some really good work. It’s suitable for any CAD environment that uses ASCII file formats containing parameters that you want to change by calculation or lookup.

Importing .wmf plots from LTspice into Word

I’ve settled on two pathways for getting LTspice plots and schematics into Word and PowerPoint documents. For schematics I usually use the “copy bitmap to clipboard” command, paste the clipboard into a suitable picture editor, and export it as a .png file. These files seem to gel well with MS Office apps, are small in size and, because they are not lossy DCT-based compressions but just run-length compacted, they have none of that blocky-heat-haze fuzziness of a .jpg, which I find so irritating on schematic imagery.

This method works OK for plots as well, but sometimes you want to get in and do extra manipulation, which isn’t practical when the data has already been rendered to a grid of pixels. So for plots I use the “export as wmf” option. This creates a Windows Metafile, a combination of pixel and vector graphics.

Now, in most modern versions of Word, when you use “insert picture from file” on one of these .wmf exports from LTspice, you get a rather disappointing pixelly rendition of the file. The appearance persists when the document is converted to pdf or html for publication. But here’s a great trick to get more out of the process. If you’ve already got an example in front of you (you have been trying this out as you read, yes?), try this. Export the frequency response plot as a .wmf, and import it into Word. You’ll probably get something that looks like Figure 6 on your screen:

Figure 6

Pasty and disappointing initial import.

 

Pasty and disappointing initial import.

 

One disappointing thing, though, is that when you use most standard pdf writers, the finely-dotted phase trace will become a solid line, and because it’s the same colour, it is hard to tell from the amplitude trace.

Here’s the magic bit. Right-click the image, and select ‘edit picture’. Straight away, Word exposes the underlying vector graphics for the image – after all, it’s the only part of the image it knows how to edit. And now, the image is composed of individual drawing elements that can be edited and even moved around (careful!). Hence the chain-dotted pink phase trace in Figure 5. Incidentally, for that plot, I shrank the image down to the right size before invoking ‘edit picture’; if you do this, Word asks you (sometimes) if you want to convert the drawing into separate objects; just answer yes and proceed as before.

So, there you are, two tips in one article for how to get the best out of LTspice and Excel working in harmony; try it out! / Kendall

2 comments on “Excel Tunes Up your Schematic Files

  1. Who Knew
    May 13, 2019

    Also worth noting, LTspice lets you parameterize component values directly from within LTspice.  You can set up conditions that drive a calculated value prior to simulation.   There's also quite a good Yahoo group dedicated to all things LTspice.  

  2. Tucson_Mike
    May 15, 2019

    Hey Kendall, 

     

    Very nice article and useful linking of LTSpice to Excel. You must have a lot of fun with this kind of capability. 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.