Counting words in a list and dictionary links

Posted on 26/09/2023 by dougaj4

This post presents two methods for extracting all the different words from a list and counting the number of occurrences of each word. The spreadsheet, including full open-source VBA code, can be downloaded from:

CountWords.zip

As an example, I have generated a list of dates starting with the current date, then each successive date between 1 and 28 days later. I have then extracted the month from each date, preceded by the month number, so the results can be sorted from January to December:

The first method uses a User Defined Function (UDF) with a scripting dictionary to extract all the unique words, then count the number of occurrences of each. It also has an option to sort the results, either in alphabetical order, or by number of occurencies:

The main code for the UDF is shown below:

Function CountWords(WordList As Variant, Optional Sortby As Long = 0, Optional Order As Long = 1) As Variant
    Dim WordCount As Scripting.Dictionary
    Dim NumRows As Long, ItemVal As Long, sWord As Variant, RtnA() As Variant
    Dim i As Long, NumRes As Long, key As Variant
    
    ' Convert WordLIst to variant array
    WordList = WordList.Value2
    
    NumRows = UBound(WordList)
    
    ' Set up WordCount dictionary
    Set WordCount = New Scripting.Dictionary

    For i = 1 To NumRows
        sWord = WordList(i, 1)
        If sWord <> Empty Then
            If WordCount.Exists(key:=sWord) = False Then
                WordCount.Add sWord, 1
            Else
                ItemVal = WordCount.Item(sWord) + 1
                WordCount.Remove sWord
                WordCount.Add sWord, ItemVal
            End If
        End If
    Next i
    
        
    NumRes = WordCount.Count
    ReDim RtnA(1 To NumRes, 1 To 2)
    
    i = 1
    For Each key In WordCount.Keys:
        RtnA(i, 1) = key
        RtnA(i, 2) = WordCount.Item(key)
        i = i + 1
    Next
    
    If Sortby > 0 Then RtnA = SortV(RtnA, Sortby, Order)
    
    Set WordCount = Nothing
    CountWords = RtnA
End Function

Code for the SortV function is included in the download file.

Note that a reference to the scripting dictionary library must be enabled. In the VBA editor, select Tools-References, scroll down to Microsoft Scripting Runtime, and select.

The second option is to use the built-in UNIQUE function, available in recent releases of Excel, and then use COUNTIF to count the number of occurrences of each word.

For more examples using the VBA scripting dictionary, and links to detailed help, see:

Dictionary link and

All about dictionaries

Posted in Excel, UDFs, VBA | Tagged , , , , , , , | Leave a comment

3DFrame-py update

Posted on 03/09/2023 by dougaj4

The Python 3DFrame spreadsheet has been updated to allow for analysis including torsional warping effects. The new spreadsheet and associated Python files can be downloaded from:

3DFrame-py.zip

The download file includes four sample spreadsheets comparing analysis results with output from Strand7 and Mastan2. The first analysis was the small concrete frame described in the Mastan2 post. This has been updated using steel I sections, as shown below.

The steel section properties were taken from the Mastan2 library, with units set to mm and Newtons, using the sections M318x18.5, M200x9.2, M250x11.2, and M250x13.4. The section properties generated in Mastan2 were copied to the spreadsheet as shown below:

The analysis in Strand7 (torsional warping not included) used m and kN units, with the results shown below:

Node deflections

Reactions

For the analysis including torsional warping the following changes were made to the model, to suit the default input options in Mastan2:

  • The coordinates were modified so that the Y axis was the vertical direction, as described in the previous post.
  • The I beam section properties were generated with the web parallel to the vertical (Y) axis.
  • Applied loads were modified to constant loads over the full member length, with just one point moment load applied at a node.
  • All section properties and loads were converted to mm and Newton units.

The spreadsheet and Mastan2 results with torsional warping effects included are shown below, with good agreement between the two results.

Changing the spreadsheet analysis to “Exclude Warping” differences from the Mastan2 reaction results are shown below:

In this case there are significant differences in the Mz/My moments (i.e the torsion in the frame vertical members), but differences in other actions are small.

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Newton, PyXLL, Strand7 | Tagged , , , , , , , | 1 Comment

Python in Excel – two views

Posted on 02/09/2023 by dougaj4

Microsoft recently announced that it is now possible to access Python from within Excel worksheets. This functionality is currently only available to Excel 365 users on the Insiders – Beta channel, who can access it at no cost. When it is released to all it will be an additional subscription service.

It was announced with a good deal of hype by Microsoft, but here are two (very different) reviews from other sources.

Tony Roberts (the author of pyxll) provides a detailed review and comparison with pyxll at:
What are the differences between PyXLL and Microsoft’s “Python in Excel”?

In summary he says:

This is a new product for Microsoft, but our initial impression is that it is disappointing. It is our belief that the design decisions taken will, if used, result in less maintainable, error prone, and slower spreadsheets.

Microsoft have added a new “PY” function that will execute arbitrary Python code, remotely in an Azure container running their own Anaconda distribution. We all know the problems associated with VBA code being coupled with workbooks, and now with this PY function the situation is worse as a spreadsheet can contain any Python code in any cell, so code will end up copied and pasted everywhere in a way that will be impossible to maintain. You can’t structure your code into packages, all of your code is written in the cells in the workbook.

This is virtually the complete opposite approach taken by PyXLL, which is to get code out of Excel to a place where it can be shared and re-used across workbooks. This gives Excel users a clean, manageable toolkit of Excel functions, written in Python. PyXLL functions run in your own Python environment and so you can import and use your own packages.

… but see the link for full details and a detailed comparison of pyxll and Python-in-Excel features.

For a different viewpoint, see the YouTube video below from Leila Gharani, which gives an enthusiastic summary of the capabilities of the new feature, together with a demonstration of some of the basic ways it can be used.

Posted in Excel, Link to Python, Python Pandas, PyXLL | Tagged , , , , | 2 Comments

Mastan2

Posted on 26/08/2023 by dougaj4

Mastan2 is a free frame analysis program based on Matlab. The linked site provides alternative downloads for those with or without Matlab installed. The site also provides a link to the book Matrix Structural Analysis, 2nd Edition, by McGuire, Gallagher, and Ziemian, which provided a theoretical and numerical basis for the analysis routines in the program. The book is available as a pdf at no cost, or US$30 for a print version.

My main reason for interest in this program is that it provides analysis of torsional warping effects in frame structures, which I am adding to my 3DFrame spreadsheet. To check the results of my spreadsheet I have analysed the small 3D frame used in previous exercises, and compared the results with and without restraint of torsional warping effects.

When comparing different 3D frame analysis programs it is important to ensure that they use the same conventions for the orientation of beam local axes, or if not to adjust the frame set-up to provide equivalent models. In 3DFrame the alignment of the beam local axes is based on the same principles as used in the Strand7 FEA program, as described in more detail here:

Local Axis 3 is aligned with the longitudinal axis of the beam, from node 1 to node 2.  Axis 2 is perpendicular to Axis 3 and parallel to the X-Y plane, so the Z axis is treated as vertical.  Axis 1 completes the orthogonal system, being perpendicular to both Axis 3 and Axis 2

The Strand7 version of the frame model examined in this post is shown below with local axes displayed:

In Mastan2 the principles used are not clearly stated (as far as I could see), but the program assumes that gravity loads are in the Y direction, so Y was treated as the vertical axis. The local axes are then defined as:

  • The local x axis is along the longitudinal axis of the member.
  • The local z axis is perpendicular to the x axis, and parallel to the global XZ plane.
  • The local y axis forms the third axis of the orthogonal system.

The frame coordinates were modified for Mastan2 so that the Y axis was vertical, as shown below:

The Mastan2 x, y and z local axes are equivalent to Strand7 local axes 3, 1, 2. When entering section properties the Strand7 values I1 and I2 are equivalent to Iyy and Izz in Mastan2, but note that the values are entered in reverse order (Izz then Iyy) on the Mastan2 input screen:

Input of the 3DFrame member properties follows the same conventions as Strand7:

In Mastan2 the materials properties are entered separately, and the density value is entered in force units, rather than mass.

In Mastan2 loads must be applied as node loads or distributed loads along the full length of the member. For this exercise distributed loads were applied in the global axes directions, plus gravity loads. The loads applied in Mastan2 must be adjusted as follows:

  • Vertical loads must be applied in the Y direction, rather than Z.
  • The Mastan2 Z axis is equivalent to the Strand7 Y axis, but in the opposite sign, so all loads on this axis must have their sign reversed.
  • The X axes in Mastan2 and Strand7 are equivalent.

The frame analyses were initially run with warping disabled. The 3DFrame Spreadsheet results are shown below, with Mastan2 results pasted below, and the % difference between the results after adjustment:

  • Y and Z axis results are swapped.
  • The Mastan2 Z axis results have the opposite sign to the spreadsheet Y axis.
  • Spreadsheet rotations are in degrees, but Mastan2 rotations are in radians.

Enabling warping analysis in the spreadsheet resulted in large changes in deflections. Note that the warping stiffness used was much higher than the real value for rectangular concrete sections, to magnify warping effects.

Importing the Mastan2 results with warping enabled shows an excellent match for all nodes and directions:

The updated 3DFrame spreadsheet will be available for download shortly, after completion of further checking.

Posted in Beam Bending, Concrete, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Newton, PyXLL, Strand7 | Tagged , , , , , , , | 1 Comment

Concrete 2023

Posted on 15/08/2023 by dougaj4

The Concrete Institute of Australia’s biennial conference is almost upon us. This year it is being held in Sydney from 10-13 September.

CONCRETE 2023

Look below at what’s in store at Concrete 2023.  

Keynote and Invited Speakers We have put together a huge line up of local and international speakers who will bring a wealth of knowledge with respect to our theme Resilient and Sustainable Concrete – Breaking Down Barriers. In particular our lead keynote speaker, Romilly Madew, CEO of Engineers Australia, will start the conference with the topic “Future concrete solutions: Improving resilience and sustainability in the built environment”, and she will be joined by speakers from Australia, Japan, Netherlands, Canada, and the USA.   You can view the entire line up at Keynote and Invited Speakers  

Technical Program The technical program is jam packed with quality research papers, innovative findings, industry updates and interesting case studies. The 4 parallel technical sessions over the main 3 days of the conference will have something for everyone in the concrete industry.   You can view the current program at Technical Program  

Exhibition Our CIA industry exhibition is a SELL OUT and will be a fantastic place to catch up with the latest in concrete materials, products, innovation, and application. We have an amazing range of organisations and sectors represented at the Exhibition, as well as a number of wonderful sponsors including our Conference Partners, Sika.   See who’s going to be at Concrete 2023 at Sponsors and Exhibitors  

Social & Networking Events As always Concrete 2023 will host several networking events including the CIA NEXGen reception on Sunday night, the CIA Women in Concrete Welcome Reception, and our flagship event, the Gala Dinner and Awards for Excellence in Concrete. Full registration gets you a ticket to all of these events, and you can also purchase separate tickets.   Check out what’s happening at Concrete 2023 Networking  

Durability Workshop Our Durability Workshop on Sunday 10 September at Curtin University has an incredible line up of durability experts from all over the world. Never before has such a team of durability experts been under the same roof in Australia, and this is an incredible opportunity to come and learn from the best, and to connect with a global network. If you’re making the trip to Perth, you should make some time to be at the workshop!   To find out more, and to register (Concrete 2023 delegates get a discount) go to Durability Workshop.   There’s so much on offer at Concrete 2023 and we don’t want anyone to miss out. If you haven’t registered yet there’s still time, and we would love to see you and your colleagues as we gather for the biggest concrete conference in the southern hemisphere in Perth next month.  

Register Here for Concrete 2023
Posted in Concrete, Newton | Tagged , , | Leave a comment