Monday 19 October 2015

Extracting wordpress posts from xml export file

A variation of the MS Excel spreadsheet, schGoogleToolBox.xls, that I wrote for extracting posts from the Blogger xml export file. This version extracts from the wordpress xml export file, slightly more complicated as uses namespaces and CDATA elements.

The objective was to extract the individual posts from the xml file, then import into Scrivener, unfortunately wordpress does strange things with html paragraph marks.  Also as I am working on MS Windows my html that I wrap the exported data into to create a valid html file, resulted in combined DOS and Unix end of line markers. The wordpress post in the CDATA section contains only linefeed characters (LF) not carriage return (CR) and line feed,  also no paragraph <p> markers, so the files as imported into Scrivener lost paragraph spacing.

To resolve this I was just going to open and convert in UltraEdit Studio but that seemed time consuming for several files, therefore modified the vba code to replace the double LF's with, double CRLF's and double <br>  codes. This produced acceptable paragraph spacing when the html files are imported to Scrivener as and converted to plain text.

The spreadsheet also retrieves categories and tags, and classes these as keywords and counts the frequency of assignment.

It only extracts posts with post_type='post', the vba code can be modified to get static pages, or if have a woocommerce site can extract products.

Spreadsheet can be down loaded: schWordPressToolBox.xls



Revisions:
[19/10/2015] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

Sunday 28 June 2015

Post Footing Design : Rutledge Formula

{Draft: thought I'd uploaded this already}




The spreadsheet can be downloaded free of charge from MiScion Pty Ltd: Rutledge.xls

Post Footing Design : Pocket Footing

{Draft: Thought I'd uploaded this already}




Spreadsheet can be downloaded free of charge at Miscion Pty Ltd : PocketFooting.xls

Monday 22 June 2015

One eWorkBook a Month

Not actually created at the rate of one a month, but representative of what could be done. Most however were created over a single weekend during the past 25 years or so: some started in As Easy As, and others in QPro. After the initial creation they were then expanded, on an as needs basis.


Period Category Description File Name
1 Function Library ... schTechLIB.xla
2 Loading ... as4055.xls
3 Loading ... schWindAssessment_r02.xls
4 Materials Library ... schStruMtrl.xls
5 Materials Library ... schTimberMatrl.xls
6 Member Design ... schDsgn4600.xls
7 Member Design ... schDsgn4100.xls
8 Member Design ... schDsgn1720.xls
9 Dimension & Geometry ... schAcadLTCivilScriptWriter.xls
10 Dimension & Geometry ... schDrawSection.xls
11 Structural Design ... schGableCanopyTimber.xls
12 Structural Analysis ... schKleinlogel03.xls
(c)Copyright 2015 Steven Conrad Harrison

Revisions:

One eWorkBook a Week

The first 25 not actually created at the rate of one a week, but representative of what could be done. Most however were created over a single weekend during the past 25 years or so: some started in As Easy As, and others in QPro, now all in MS Excel. After the initial creation they were then expanded, on an as needs basis.

This list is a work in progress and I will update as I release the workbooks. {NB: The links on the file names lead to posts with more detail about the workbook. See post link at bottom of page for bundled package. Those without a blog post link can be found on my ExcelCalcs profile}

Period Type Category Description File Name
1 Function Library vba Functions for Structural Design schTechLIB.xla
2 Loading Simplified Wind Loading as4055.xls
... Loading Simplified Wind Loading as4055v1.xls
3 Loading Wind Loading to AS1170.2 schWindAssessment_r02.xls
4 Materials Library Steel Sections/Materials Properties schStruMtrl.xls
5 Materials Library Timber Sections/Materials Properties schTimberMatrl.xls
6 Member Design Cold-formed Steel Design schDsgn4600.xls
7 Member Design Steel Design schDsgn4100.xls
8 Member Design Timber Design schDsgn1720.xls
9 Dimension & Geometry Long Section Profile Plotter schAcadLTCivilScriptWriter.xls
10 Dimension & Geometry Draw Cold-Formed Sections schDrawSection.xls
11 Structural Design Doubly Pitched Timber Canopy schGableCanopyTimber.xls
12 Structural Analysis Kleinlogel Formula  schKleinlogel03.xls
13 ... Planner Calendar. schPlannerCalendar.xls
14 ... IE: Work study flow process chart. schWorkStudy.xls
15 ... ... bomStructureTreeStage3.xls
16 ... IE/POM/CAPM Automatic Explosion of Bill of Materials. mrpBOMv2.xls [ExplodedBOM.zip]
17 ... ... schBOMStructureTreeStage1.xls
18 ... Experiments with Parametric Sketches using XY Charts. drawWorkSheet2009.xls
19 ... Dimension and Geometry of Gable Frame shed Frame Member Lengths and Bracing Lengths schBuildingDimensions.xls
20 ... CADD schCADDv2.xls
21 ... Estimating: Hole punching requirements for roll-formed sections. schHolePunching.xls
22 ... Beaufort wind Scale Beaufort.xls
23 ... Example Using Circular References to Force Iteration: Calculation Effective Section Modulus for Coldformed C-Section to AS4600. schColdformedCee.xls
24 ... Tables for strength of bolted joints in thin cold-formed steel sheets to AS4600. schTechNote022pt2.xls
25 ... Wind Loads on Gable Frame to Australian Wind Code AS1170.2 schShedDesignerR01.xls
26 ... Experiments with Geographical Information System (GIS) central places centralPlaces4.zip
27 ... VBA Experiments with Abstract Data Structures. dataStruct.zip
28 ... CAD: Automatic generation of framing plans and elevations simple gable frame. drawShed.zip
29 ... CAD: Experiments with DesignCAD: Draw 3D framing of American Barn type structure. drawShedDC1.zip
30 ... VBA Experiments with ACAD Script Automation. drawTut.zip
31 ... VBA Experiments with Excel Shapes Layer: Structural Framing Plans. ExcelShapes.zip
32 ... Application for Generation of Height Span Charts Gable Frame Sheds. schDesignEngineR01.zip
33 ... VBA Experiments Parsing ACAD DXF files vbaDXF.zip
34 ... VBA Experiments with Class Objects. vbaObjects.zip
35 ... Excel/VBA Tutorials. vbaTuts.zip
All the above are available as separate downloads from ExcelCalc's or from my blog's as a single bundled package {see post link at bottom of page}.

Beyond ExcelCalcs

Today 23/06/2015 is week 26 for 2015, so with the above now upto date with respect to week 26, should now be able to release one workbook per week. Changed my mind [28/6/2015], decided to list all the spreadsheets that I have released already: turns out that exceeds 52 spreadsheets, so the rest will be listed on the one workbook per day challenge.

The workbooks listed below are available via this blog and and some over on MiScion Pty Ltd.

Period Type Category Description File Name
36 ... ... bcaImportanceLevels.xls
37 ... ... BuiltUpSection.xls
38 ... ... dbExperiments.xls
39 ... ... exportNamedToXML.xls
40 ... ... importXML.xls
41 ... ... PocketFooting.xls
42 ... ... Rutledge.xls
43 ... ... schGoogleToolBox.xls
44 ... ... surfaceRoughnessLength.xls
45 ... ... treeExperiments2014.xls
46 ... ... wbk6dsgnBarrierPost.xls
47 ... ... windBarrierPost.xls
48 ... ... windRiskAssessment2014.xls
49 ... ... xmlExperiments.xls
50 ... ... backEndPFrame.xls
51 ... ... frontEndPFrame01.xls
52 ... ... stairCalculator.xls

Revisions:
[22/06/2015] : Original
[23/06/2015] : Split the table
[24/06/2015] : Added Type Column to Tables
[28/06/2015] : Added Spreadsheets I have previously released.
[30/06/2015] ; Added Extra Links and Descriptions

One eWorkBook a Day

Not actually created at the rate of one a day, but representative of what could be done. Most however were created over a single weekend during the past 25 years or so: some started in As Easy As, and others in QPro. After the initial creation they were then expanded, on an as needs basis.

This list is a work in progress and I will update as I release the workbooks.

Period Type Category Description File Name
1 Function Library vba Functions for Structural Design schTechLIB.xla
2 Loading Simplified Wind Loading as4055.xls
... Loading Simplified Wind Loading as4055v1.xls
3 Loading Wind Loading to AS1170.2 schWindAssessment_r02.xls
4 Materials Library Steel Sections/Materials Properties schStruMtrl.xls
5 Materials Library Timber Sections/Materials Properties schTimberMatrl.xls
6 Member Design Cold-formed Steel Design schDsgn4600.xls
7 Member Design Steel Design schDsgn4100.xls
8 Member Design Timber Design schDsgn1720.xls
9 Dimension & Geometry Long Section Profile Plotter schAcadLTCivilScriptWriter.xls
10 Dimension & Geometry Draw Cold-Formed Sections schDrawSection.xls
11 Structural Design Doubly Pitched Timber Canopy schGableCanopyTimber.xls
12 Structural Analysis Kleinlogel Formula  schKleinlogel03.xls
13 ... ... schPlannerCalendar.xls
14 ... ... schWorkStudy.xls
15 ... ... bomStructureTreeStage3.xls
16 ... ... mrpBOMv2.xls
17 ... ... schBOMStructureTreeStage1.xls
18 ... ... drawWorkSheet2009.xls
19 ... ... schBuildingDimensions.xls
20 ... ... schCADDv2.xls
21 ... ... schHolePunching.xls
22 ... ... Beaufort.xls
23 ... ... schColdformedCee.xls
24 ... ... schTechNote022pt2.xls
25 ... ... schShedDesignerR01.xls

Beyond ExcelCalcs

Block of workbooks need to Supply to catch up with current date in 2015, as at 22/6/2015. Assuming 30 days to catch up, that will be around 200 workbooks need to supply in 30 days, or about 7 workbooks per day: which may just be about possible. Otherwise move from calender year to financial tax year 2015 July to 2016 June.

Period Type Category Description File Name
26 ... ... ...
27 ... ... ...
28 ... ... ...
29 ... ... ...
30 ... ... ...
31 ... ... ...
32 ... ... ...
33 ... ... ...
34 ... ... ...
35 ... ... ...
36 ... ... ...
37 ... ... ...
38 ... ... ...
39 ... ... ...
40 ... ... ...
41 ... ... ...
42 ... ... ...
43 ... ... ...
44 ... ... ...
45 ... ... ...
46 ... ... ...
47 ... ... ...
48 ... ... ...
49 ... ... ...
50 ... ... ...
51 ... ... ...
52 ... ... ...
53 ... ... ...
54 ... ... ...
55 ... ... ...
56 ... ... ...
57 ... ... ...
58 ... ... ...
59 ... ... ...
60 ... ... ...
61 ... ... ...
62 ... ... ...
63 ... ... ...
64 ... ... ...
65 ... ... ...
66 ... ... ...
67 ... ... ...
68 ... ... ...
69 ... ... ...
70 ... ... ...
71 ... ... ...
72 ... ... ...
73 ... ... ...
74 ... ... ...
75 ... ... ...
76 ... ... ...
77 ... ... ...
78 ... ... ...
79 ... ... ...
80 ... ... ...
81 ... ... ...
82 ... ... ...
83 ... ... ...
84 ... ... ...
85 ... ... ...
86 ... ... ...
87 ... ... ...
88 ... ... ...
89 ... ... ...
90 ... ... ...
91 ... ... ...
92 ... ... ...
93 ... ... ...
94 ... ... ...
95 ... ... ...
96 ... ... ...
97 ... ... ...
98 ... ... ...
99 ... ... ...
100 ... ... ...
101 ... ... ...
102 ... ... ...
103 ... ... ...
104 ... ... ...
105 ... ... ...
106 ... ... ...
107 ... ... ...
108 ... ... ...
109 ... ... ...
110 ... ... ...
111 ... ... ...
112 ... ... ...
113 ... ... ...
114 ... ... ...
115 ... ... ...
116 ... ... ...
117 ... ... ...
118 ... ... ...
119 ... ... ...
120 ... ... ...
121 ... ... ...
122 ... ... ...
123 ... ... ...
124 ... ... ...
125 ... ... ...
126 ... ... ...
127 ... ... ...
128 ... ... ...
129 ... ... ...
130 ... ... ...
131 ... ... ...
132 ... ... ...
133 ... ... ...
134 ... ... ...
135 ... ... ...
136 ... ... ...
137 ... ... ...
138 ... ... ...
139 ... ... ...
140 ... ... ...
141 ... ... ...
142 ... ... ...
143 ... ... ...
144 ... ... ...
145 ... ... ...
146 ... ... ...
147 ... ... ...
148 ... ... ...
149 ... ... ...
150 ... ... ...
151 ... ... ...
152 ... ... ...
153 ... ... ...
154 ... ... ...
155 ... ... ...
156 ... ... ...
157 ... ... ...
158 ... ... ...
159 ... ... ...
160 ... ... ...
161 ... ... ...
162 ... ... ...
163 ... ... ...
164 ... ... ...
165 ... ... ...
166 ... ... ...
167 ... ... ...
168 ... ... ...
169 ... ... ...
170 ... ... ...
171 ... ... ...
172 ... ... ...


Caught up with current date, and should now be able to produce one workbook per day.

Period Type Category Description File Name
173 ... ... ...
174 ... ... ...
175 ... ... ...
176 ... ... ...
177 ... ... ...
178 ... ... ...
179 ... ... ...
180 ... ... ...
181 ... ... ...
182 ... ... ...
183 ... ... ...
184 ... ... ...
185 ... ... ...
186 ... ... ...
187 ... ... ...
188 ... ... ...
189 ... ... ...
190 ... ... ...
191 ... ... ...
192 ... ... ...
193 ... ... ...
194 ... ... ...
195 ... ... ...
196 ... ... ...
197 ... ... ...
198 ... ... ...
199 ... ... ...
200 ... ... ...
201 ... ... ...
202 ... ... ...
203 ... ... ...
204 ... ... ...
205 ... ... ...
206 ... ... ...
207 ... ... ...
208 ... ... ...
209 ... ... ...
210 ... ... ...
211 ... ... ...
212 ... ... ...
213 ... ... ...
214 ... ... ...
215 ... ... ...
216 ... ... ...
217 ... ... ...
218 ... ... ...
219 ... ... ...
220 ... ... ...
221 ... ... ...
222 ... ... ...
223 ... ... ...
224 ... ... ...
225 ... ... ...
226 ... ... ...
227 ... ... ...
228 ... ... ...
229 ... ... ...
230 ... ... ...
231 ... ... ...
232 ... ... ...
233 ... ... ...
234 ... ... ...
235 ... ... ...
236 ... ... ...
237 ... ... ...
238 ... ... ...
239 ... ... ...
240 ... ... ...
241 ... ... ...
242 ... ... ...
243 ... ... ...
244 ... ... ...
245 ... ... ...
246 ... ... ...
247 ... ... ...
248 ... ... ...
249 ... ... ...
250 ... ... ...
251 ... ... ...
252 ... ... ...
253 ... ... ...
254 ... ... ...
255 ... ... ...
256 ... ... ...
257 ... ... ...
258 ... ... ...
259 ... ... ...
260 ... ... ...
261 ... ... ...
262 ... ... ...
263 ... ... ...
264 ... ... ...
265 ... ... ...
266 ... ... ...
267 ... ... ...
268 ... ... ...
269 ... ... ...
270 ... ... ...
271 ... ... ...
272 ... ... ...
273 ... ... ...
274 ... ... ...
275 ... ... ...
276 ... ... ...
277 ... ... ...
278 ... ... ...
279 ... ... ...
280 ... ... ...
281 ... ... ...
282 ... ... ...
283 ... ... ...
284 ... ... ...
285 ... ... ...
286 ... ... ...
287 ... ... ...
288 ... ... ...
289 ... ... ...
290 ... ... ...
291 ... ... ...
292 ... ... ...
293 ... ... ...
294 ... ... ...
295 ... ... ...
296 ... ... ...
297 ... ... ...
298 ... ... ...
299 ... ... ...
300 ... ... ...
301 ... ... ...
302 ... ... ...
303 ... ... ...
304 ... ... ...
305 ... ... ...
306 ... ... ...
307 ... ... ...
308 ... ... ...
309 ... ... ...
310 ... ... ...
311 ... ... ...
312 ... ... ...
313 ... ... ...
314 ... ... ...
315 ... ... ...
316 ... ... ...
317 ... ... ...
318 ... ... ...
319 ... ... ...
320 ... ... ...
321 ... ... ...
322 ... ... ...
323 ... ... ...
324 ... ... ...
325 ... ... ...
326 ... ... ...
327 ... ... ...
328 ... ... ...
329 ... ... ...
330 ... ... ...
331 ... ... ...
332 ... ... ...
333 ... ... ...
334 ... ... ...
335 ... ... ...
336 ... ... ...
337 ... ... ...
338 ... ... ...
339 ... ... ...
340 ... ... ...
341 ... ... ...
342 ... ... ...
343 ... ... ...
344 ... ... ...
345 ... ... ...
346 ... ... ...
347 ... ... ...
348 ... ... ...
349 ... ... ...
350 ... ... ...
351 ... ... ...
352 ... ... ...
353 ... ... ...
354 ... ... ...
355 ... ... ...
356 ... ... ...
357 ... ... ...
358 ... ... ...
359 ... ... ...
360 ... ... ...
361 ... ... ...
362 ... ... ...
363 ... ... ...
364 ... ... ...
365 ... ... ...

Revisions:

eWorkBook Challenge: spreadsheets and other calculation workbooks

Over past few years noticed several challenges such as a photo a day, a sketch a day, and 500 words a day, some of these challenges are based on a year, others over shorter periods such as a month or week.  A quick search of the internet didn't reveal any similar challenges for spreadsheets or other calculation workbooks such as MathCAD, in the main the challenges are to create an electronic workbook [eWorkBook] for a specific purpose, or otherwise use spreadsheets to track progress with other challenges: such as  tracking progress  writing a 50,000 word novel in a month at an average rate of 1667 words/day. It would therefore seem there is no existing challenge for creating a collection of spreadsheets.

May be such challenge isn't considered appropriate, or it is just too complicated for an individual to pursue. Most people don't have a need for a large variety of spreadsheets, they just produce multiple spreadsheets based on one single template. Those people working in technical fields probably already have the eWorkbooks they need for their work, whilst others create new eWorkbooks everyday in the course of their daily work. However it is also clear that people are looking for technical spreadsheets to carry out various engineering calculations. So there are people who can use eWorkbooks but not build them, or otherwise don't want to put the effort in at the time they need them.

By now I would expect that most graduates from formal educational programmes would have a toolbox of eWorkBooks for the types of tasks they will commonly encounter. Though unfortunately a lot of those they create as a formal part of their courses are probably created in software the likes of MathCAD and/or MatLAB: the problem with such is that such software is expensive and not all that readily available in organisations. Spreadsheets however are a lot more readily available, and therefore the better choice for building a toolbox of calculation tools.

The challenge isn't intended to be a competition with winners and losers, there are no prizes. The challenge is a personal goal, that anyone can take up, the prize if any is a useful personal collection of calculation tools. If wish to share such tools then I recommend up loading them to ExcelCalcs. The challenge can be spread over any time frame, suggestions are:
  1. One eWorkBook a Month (12 for a year)
  2. One eWorkBook a Week (52 for a year)
  3. One eWorkBook a Day (365 for a year)
Which provides the opportunity to spread the challenge over a period of 3 years, the first year starting with one spreadsheet a month, then slowly building up to one spreadsheet a day for the third year.

I have already uploaded 37 unique files to my ExcelCalcs profile, so that represents enough spreadsheets to cover one workbook a month. Given that some of these are zip files, with at least one containing more than 40 workbooks, that also means enough workbooks to cover one workbook a week. Searching my archive hard-drive I find over 70,000 workbooks of various formats (As Easy As, QPro DOS, QPro Windows, MS Excel), many of those are duplicates or minor variations on a theme. My main toolbox already holds over 700 MS Excel spreadsheets. So producing some 365 eWorkBooks doesn't seem an impossible challenge: though creating something unique from scratch each and every day could be challenging. However the challenge doesn't have to be about creating from scratch, for me the challenge would be cleaning up the existing spreadsheets and reformatting so that they are suitable for release for others to use.

eWorkBooks, spreadsheets or otherwise can be classified into the following types:

TYPE 1: Reference Library
These eWorkBooks are databases or tables of data which are largely constant, and used as lookup values for calculations. They include such things as materials libraries, properties of chemical elements and such things. {MS Access or other DBMS potentially the better tool for this}

TYPE 2: Data Collection
These are eWorkBooks that are used to collect variable data, and create data tables and simple lists. Examples would include book lists and price lists. {MS Access or other DBMS potentially the better tool for this.}

TYPE 3: Tabulated Calculations
Tables of independent parameters and calculated dependent variables, with or without charts or graphs illustrating such relationships. {MS Access or other DBMS potentially the better tool for this.}

TYPE 4: Text Book Formula Sheets
These are the type of eWorkBooks typically uploaded to ExcelCalcs and complying with their recommended template making use of XLC. Such worksheets typically restricted to a single A4 page, have a sketch to illustrate the parameters of the mathematical model, show the algebraic formula, show the numbers substituted in the formula and then show the numerical result. These are primarily for the purpose of studying and understanding the formula.

TYPE 5: Calculated Reports
These are full technical reports, at least as far as the calculations go. Simply combining a series of Type-4 spreadsheets would not be appropriate for such reports. Such approach would generate a large amount of paper if so printed, or waste of hard-disk space if printed to pdf files, and most importantly a waste of time to read. Such calculated reports should be kept compact and concise, details of calculations are hidden behind the scenes, the reports display input parameters, and calculated results, but not necessarily the process of transformation from input to result. The user of such reports is expected to make independent checks on the results using other tools.

TYPE 6: Calculators and Dashboards
These can be simple or complex, either way no printed report is generated. These obtain input parameters and calculate results, how such transformation takes place is not shown. The purpose of such tools is rapid decision making. Such tools are likely used by persons checking Type-5 workbooks.

TYPE 7: Applications
These eWorkBooks can make little to extensive use of visual basic for applications (vba) and custom dialogue forms. The main features are that the workbook has the simple interface of Type-6 calculators, but can produce the reports of Type-5, or otherwise more detailed trace similar to Type-4, as well as produce tabulated reports based on variations in magnitude of input parameters similar to Type-3. Also likely to include data collection (Type-2), and rely on information extracted from libraries (Type-1).

TYPE 8: Function Libraries
Libraries of additional functions., which extend or otherwise simplify the calculation capabilities of the primary workbook application (eg, MS Excel).

TYPE 9: Utility Library/Add-In
Extends the features of the primary application (eg.MS Excel), but not the calculation capability. (eg. XLC).

TYPE:10: Other
Those types pf spreadsheets not mentioned elsewhere.


A spreadsheet to plan and track spreadsheet creation can be downloaded here.


Revisions:

  1. [22/06/2015] Original
  2. [23/04/2016] Change download links to MiScion Pty Ltd Web Store.