officeAutomata Updates

 
OfficeAutomata_Blue.png
 

Over the last couple months, officeAutomata has been developing new products & capabilities and we are working hard on an official release.  After interviewing and evaluating with our current customers, officeAutomata has decided to expand our focus. We are pivoting from our Microsoft Excel focus, based on feedback from Chief Technical Officers and Chief Operating Officers in Business Process Outsourcing companies.  We will keep technical support for our Excel products and wish to thank all those involved in testing for their support over the years.

Our new product will be enterprise focused and very much like our previous products will aim to streamline & automate processes and cut down the time needed to accomplish tasks.  To go along with this release, we’ll be updating our official site officeautomata.com to reflect the company’s new market focus. Our aim continues to always be five steps ahead of the crowd. We hope you all keep us in mind and stay tuned as we send out updates on our next move.

Signed,
Jeremiah

officeAutomata Founder

VBA with Cortana in Excel

Hey everyone! Visual Basic for Applications (VBA) is used in Excel everywhere, so I wanted to make it easy to use VBA macros with Cortana. Here's a brief demonstration of how I use VBA with Cortana in Excel.

This demo uses officeAutomata (for memory and actions) integrated with Windows Cortana (for voice controls). Below is a list of the main actions shown in the demo.

Let me know your thoughts in the comments below, and any suggestions for actions you would like Cortana to do for you.

The featured actions in the demo include:

1. Add VBA Macro

Use an easy input box to load your Macro into Cortana's memory

2. Edit VBA Macro

Call your Macro by name to edit it

3. Run VBA Macro

           Run your VBA Macro by name

4. Undo VBA Macro

            Use Cortana's version control to undo your Macro if needed

Working with Cortana in Excel

Hey everyone! Here's a brief demonstration of how I've been using Cortana in Excel for my work.

This demo uses officeAutomata (for memory and actions) integrated with Windows Cortana (for voice controls). Below is a list of the main actions shown in the demo.

Let me know your thoughts in the comments below, and any suggestions for actions you would like Cortana to do for you.

The featured actions in the demo include:

1. Copy to the end of a column/Paste at the end of column

No need to scroll down, just have Cortana find the end of the column for you.

2. Autofill Series

Autofill a series of numbers to the end of the next row.

3. Create Macro

Create a macro of the past few actions for Cortana to remember.

4. Enter text into a cell

Say what you want a cell to contain.

5. Get the current date

A function to get the current date.

6. Copy and paste values

Copy and paste of value of a cell and not the function.

7. Fill down (or any direction)

Tell Cortana what cell to fill to.

8. Insert Checkmarks

Insert the special character checkmark where you need them.

9. Create email

Create a new Outlook email from different cells and their values.

 

 

Mark’s Monday morning

Meet Mark. He’s been using Cortana for a couple of months now. It’s made his Monday mornings a whole lot easier. Most mornings, in fact. Here’s a typical Monday morning for him...

Mark sits at his desk, turns on his computer, thinking about the reports he has to review, edit and get to management that day.

He scans his Outlook for the weekly reports that came in late on Friday. Opening the workbooks, Cortana automatically loads up when Excel begins.

“Good morning, Mark, how are you?”

“Good Cortana, thanks.” Mark says, enjoying her usual greeting, although he prefers when she uses “You alright?” as it reminds him of a British friend.

“Cortana, do you have this workbook in your memory?” He asks

 “Yes, I created that with Allison, at 7pm last Friday,”

“Ok, any major changes from the previous version?”

“There were value changes in cells, D10, E10 and F10,” Cortana replies, Mark is delighted he didn’t have to go looking for this information.

“Ok, load up workbook Management Report Template,” He says

“Done.”

Mark glances through the cells Cortana has mentioned, noting that it’s just normal weekly changes, but he appreciates that it takes far less time than it did six months ago. He then spots one number that looks way off -

“Cortana, were there any pastes in this report?”

“Yes, cell D10 and E10,”

“Where was E10 copied from?”

“Workbook Company Report 8-23-16, Cell P9.”

Hmm, Allison must have had the wrong data report up, Mark thinks.

“Cortana, do you have Workbook Company Report 9-23-16, cell P9 in your memory?”

“Yes, it’s in the clipboard,”

Mark makes the quick change, glad he spotted it before any damage was done.

Most of the cells changes in the report are auto-updates from other sheets, ie reference cells, just a few cells are copied over from the ones that aren’t in the main drive.

“Ok, Cortana, highlight all the duplicate items in Column F,” Mark asks

 “Ok.”

He scans down the column of highlighted cells, it’s helpful that Cortana highlights what he needs to see in such a large report.

Mark has forgotten a function, unsure of a particular command so he clicks on the walkthroughs. He scrolls down the menu to the List Difference function, hovering over it till the tooltip pops up and shows him the command. Oh yeah, that’s what it was. He’s seen the walkthrough before, and it showed him exactly what the command did and how to do it, but right now he just needed to remember what to ask Cortana

“Cortana, find the difference between two lists,”

“Sure, what’s the first cell range?"

“B1 through B200,”

“And the second cell range?”

“C1 through C200,” Cortana highlights all the different items in lists

Great, nothing new to see. Mark’s ready to build his reports.

“Cortana, show me all my macros,” Mark requests of Cortana and she shows a display of all the macros he’s created, mainly by just doing his usual work and then telling Cortana to save the past few actions.

He created a few of them that make his report creations fast. And he can chain them together! He doesn’t know how to code, been too busy to learn, but now he doesn’t have to, he just shows Cortana what to do and she remembers it.

He clicks macros 1,5,6 and 9. That’ll copy the data over, reformat it, add some borders and different backgrounds, and then save with a new name. He clicks ok and 5 seconds later it’s done.  He attaches it to a new email and moves on to other tasks, happy in the knowledge that his digital assistant has made his day easier.

Case studies from our Beta Testing

During the course of our beta testing of Cortana for Excel, certain issues arose that we hadn’t predicted. This was particularly helpful in developing the program further and I came up with some fixes, which led to better functionality for Cortana.

We thought it might be interesting to share some examples, for those wondering about the specifics.

Beta User Mel got in contact with some suggestions and problems she was having using Cortana for Excel. The following is a list of requests she suggested:

  • Cortana select a range for me
  • Read me the contents
  • What format is this cell?
  • Let’s change that format to red/blue/green text (Or other and specify the color format on the color selection window)
  • Change format to general/number/string/text/percentage/time/phone number.etc
  • Cortana lets add conditional formatting
  • Take me to the very last cell (Equivalent of Ctrl+end)
  • Take me to the last column
  • Take me to the last row
  • Delete all blanks in column A
  • Delete the entire row of blanks in Column A
  • Delete the entire row of blanks in row 1
  • Cortana let’s insert a pivot table/pivot chart
  • Let’s get data from text
  • Let’s get data from the web
  • Let’s make this cell a drop down menu based on these cells (Data validation list)

We got to work on these requests and have currently completed 88% of these requests, with positive feedback from users. Further clarification is needed for getting data from text and from the web. This took 4 days to complete and will be added to the next version of officeAutomata.

 

Beta Tester Alex had the following requests to make his Excel experience easier:

  • Copy cells <cell> through <cell>
  • Vlookup with columns by name instead of number
  • Paste As Values

We solved these issues in less than a day and are available in the current version.

 

Beta Tester David had the following requests to make his Excel experience easier:

  • Copy the entire table
  • Create pivot table

We added these functions in less than a day and plan to include them in the next version.

Our aim is to make officeAutomata as functional and useful as possible, so our user feedback is vitally important. If there’s a suggestion or request you would like to see in the program, we’d be delighted to hear from you.

Get in touch @officeAutomata or at www.officeAutomata.com/support

 

Beta Testing Lessons Learned

We’re wrapping a beta testing here at officeAutomata, and I wanted to share some of the things we encountered and the lessons learned.

New Features added by request of Beta Users

  • Copy cells: Copy Cells <cell> through <cell>
  • Switch sheets via sheet index : Switch to Sheet Index <number>
  • Macro popup for saving, viewing all macros, chaining macros together or adding/removing events from macros
  • vLookup columns by letter instead of number
  • Close Excel, workbook and save functions separated
  • Cortana will wait to finish a command if you are editing the worksheet

Major Issues Encountered

  • The default installer for Excel Add-in’s was not compatible with installing Cortana’s interface, so a custom installer had to be developed and is now being used.
  • The voice recognition system through Cortana also had trouble being used in an Excel Add-in and extra error handling had to be encoded.

Minor Fixes:

  • Real-time vocabulary additions to Cortana for naming macros repaired
  • Minor fixes to some functions to include rows and columns
  • Macros as voice only commands had some trouble, so a pop-up box was added to show users what was being saved or about to be done
  • Switching sheets doesn’t work if Cortana didn’t have the sheet name in her database or vocabulary, so it was changed to switch by sheet index instead
  • Undo was improved to make changes more seamless to the user
  • Clarified some functions output

Lessons Learned

  • More thorough in-house testing on other production systems was required and could have spared ¼ of the issues encountered
  • Google groups worked well as a beta test center, kept everyone informed and was an easy place to post and view replies. A dedicated slack channel was unnecessary.               

Overall, because of the efforts of the beta testers, officeAutomata is nearly ready for launch. I appreciate the efforts of everyone who participated and contributed.

Part 3: Building officeAutomata with F#

(Continued from the previous post, where I discussed developing the idea for officeAutomata and started out learning how to program in VB.Net)  

VB.Net is a nice starter language, as its syntax is very approachable for a new beginner and introduces programming concepts adequately. I’m assuming most inter-office programmers have used VB writing macros for Excel. However, it does show its limits when writing longer algorithms and programs. As with most object oriented languages, it’s requires objects to get anything done, and is overly reliant on “for each” statements, while at the same time being quite verbose; like a talkative person that says a lot but communicates little. All these extra lines of codes seemed to serve little purpose to a beginner, when what I needed at the time was just some first-class functions.

In my continued learning, with Hacker News and reddit (r/Programming & r/LearnProgramming), I stumbled upon posts for F#, curiosity got the better of me, since I had never heard of it even though I was working in .Net. The posts were quite intriguing, as the benefits of using the language seemed clear. F# looked more efficient, less code required, less bugs, encourages good programming practices, and making it difficult to do unwise things. One post in particular help me make the decision to switch, which for me, simply showed the difference between the two styles of programming, OO vs Functional, and made the decision easy.

 
 F#'s new logo

F#'s new logo

Once I had decided to switch, I starting learning as much as I could through TryF#, fssnip.net, and Robert Pickering books The Beginnings/Foundations of F#. Fsharpforfunandprofit also was a great resource, especially the posts on railroad programming, which made much more sense to me than OO ever did. I started with rewriting the algorithm that I was working on in previously, into F#, along with some basic database structures to hold inputs/outputs. It was slow going at first, as I moved from an OO to functional perspective while learning a new syntax, but it became easier very quickly.

I forced myself to learn (and understand) functional concepts like records, type providers, pattern matching and recursion before moving forward too far in development as I felt these concepts were important to using F# to its full potential and would make future programming efforts easier. Since then, as I’ve developed officeAutomata, I’ve picked up scripting with F#, web development with WebSharper, asynchronous workflows, computation expressions, building types, finite state machines, actors through Mailboxes and observables. All of which have proven useful in building officeAutomata with less code and fewer bugs than comparable programs.

It wouldn’t be a stretch to say that F# has made officeAutomata possible, as building a program like this as a solo developer (and a beginner) in other languages might’ve been impossible. F# has influenced development throughout the project, as styles have adapted based on the need of portion that was being developed, from reactive, Actors or pipeline; and F# made adapting styles easy while keeping the flow of the program similar.

I’ve really appreciated the support, spirit of community,  and passion of F#er’s, whether it be at meetups in Helsinki, quick and informative responses on StackOverFlow or Sunday mornings with coffee & Sergey’s F# Weekly. It definitely has made working in F# a much more enjoyable experience than switching programming language should be!

Some of the challenges we’re using F# for here at officeAutomata include: Natural Language Processing (for processing voice commands), Reinforcement Machine Learning (for real time automation generation), Conversational User Interfaces (turning speech into intent for commands), translating Excel functions into verbal commands and meta Excel functions (from user requests). Currently, officeAutomata is in a closed beta, and we're planning on its initial release within the next few months.

We’re starting to look for initial hires with F# experience. So if you’re interesting in joining the team, take a look at open positions here www.officeautomata.com/positions

In the meantime, please subscribe to www.officeAutomata.com for updates and follow us on Twitter and LinkedIn - @officeAutomata. If you have any feedback for us, please get in touch!

7 More Uses for Cortana in Excel

Hey everyone! Here's another brief demo using officeAutomata. Based on feedback from viewers of the previous video, I’ve developed a new way to use Cortana to add functions to your workbooks.

This demo uses officeAutomata (for memory and actions) integrated with Windows Cortana (for voice controls). Below is a list of the main actions shown in the demo.

Let me know your thoughts in the comments below, and any suggestions for actions you would like Cortana to do for you.

The featured actions in the demo include:

1. Load my last Workbook

Loads the last workbook used in Cortana's memory, if you don't want to go searching for it.

2. Show me what I did last

Shows the last 3 things you in that workbook

3. Where was that pasted from?

Tells you which workbook & worksheet a copy/paste came from.

4. Ask for help

Ask about functions and learn how they’re used

5. Get help creating functions

Have Cortana walk through creating functions with you

6. Apply formatting from worksheet

Apply all the formatting based off any previous sheet in Cortana's memory.

7. Undo

Undo any command that you gave Cortan

Part 2: The challenge of doing something new

After I left working with the U.S.A.F (U.S. Air Force) in September 2013 I didn’t have an exact plan but I knew I would have to devote time to learning something new. My biggest challenge was that I had no idea how to code. I still had the dream for a J.A.R.V.I.S (Link) and realized that if I wanted a program to learn from me that it would require a machine-learning algorithm analyzing my work in real time.  

Friends didn’t quite understand what I was trying to do, from either poor communication or from following common thinking that desktop applications were dead, and encouraged me to learn JavaScript or other web-based languages, as many others were doing at the time.  This would enable me to do something with the web; it was “hot” at the time but I didn’t feel it was right for me.  

 
 University of Helsinki

University of Helsinki

I began with a month-long task of studying data mining, pattern recognition and reading as many PHD papers of algorithms related to real time event recognition as I could. That ended up totaling around 30 papers that I could understand (to a certain degree). I resolved to use an algorithm described in the paper “Discovery of Frequent Episodes in Event Sequences” from the University of Helsinki (Heikki Mannila, Hannu Toivonen, A. Inkeri Verkamo). There was pseudo-code in the paper explaining the algorithm (i.e. it wasn’t in any computer language, but more just an approximation), so I set about trying to translate the algorithm into a language.

 

This meant choosing a language. I knew I’d be working in Windows and .Net so that limited the number of languages I could choose from- C++, VB.net, C#... Visual Basic.Net looked the most approachable from a syntax perspective and I thought it would work well. 

From my own plans, I envisaged a timeline from development to a real program should be around eight months. I can hear you chuckle at your screens.  I spent a few more months tweaking the algorithm for my uses, excited about what I was creating. There were fits and starts but I felt I was getting there.

 

When it was finally translated, I ran some tests.

And the results were not what I was expecting.

Was it back to square one? Not at all, but it felt like a setback.

 

I was reading continuously, trying to soak up as much knowledge as possible and I’d also been getting more involved in the programming blog-sphere. Some friends were great in giving advice too and I came to the realization that while VB.net is accessible and bears many similarities to C# - if I kept up with it, there would be difficultly later in getting other programmers involved, as very few people want to work in VB.net anymore. 

During my research I discovered another .net language that had been getting rave reviews. It also seemed to have a great following with a supportive community. It also appeared to have more possibility and capability mathematically – something that I required. This was F#.

Could it produce the results I wanted? 

 

I’ll let you know in the next blog post. In the meantime, please subscribe to www.officeAutomata.com for updates and follow us on Twitter and LinkedIn - @officeAutomata. If you have any feedback for us, please get in touch!

Why I developed officeAutomata - Cortana for Excel

It’s a question that keeps arising these days, which I find surprising. Why did I develop officeAutomata? What, particularly, drove me so much to distraction that I felt I had to create my own tool? Mostly when people ask this they add their stories of frustration and how they would have, if they could have or if they had the time. So, I thought it would be a neat introduction to let you know some of the history of why I started on this path.

Back in 2010, I was working as a program manager in the U.S Air Force. I worked on development projects and my primary tool was MS Office; Word for contract development, PowerPoint for staff presentations and like millions of workers across the U.S and the globe, I relied heavily on Excel for accounting and budget management, incentive fee management, Risk management, logistics management, hardware/software specification management etc…

 Lots of meetings like this...

Lots of meetings like this...

All of the programs we use everyday have their annoying quirks but with PowerPoint and Word, regular usage unveiled solutions. Excel remained the big mystery and I found I wasn’t alone. Given that it’s the most widely used data and accounting management tool (Rexer Analytics, 2013, BARC Research The Planning Survey, March 2014) in the world, it’s surprising how little the average user knows about it. The capabilities of Excel are large but most of us aren’t using them. I had a minor background in computing – using Linux, some HTML/CSS, regular Windows system maintenance etc. but as I worked to increase my skills with Excel I found it challenging and discovered that it quickly progresses into complex math or programming with Visual Basic and I didn’t think I required that level. There were things I was doing manually when I could have been using macros or VLookUp’s and IF statements.

 

On a day-to-day basis I was pretty happy but in the big picture I understood that as technology developed, some of the work I was carrying out could be automated. First I thought it would be helpful to have an assistant who could teach me the things I wasn’t aware of in Excel, then I dreamed about a J.A.R.V.I.S type who could watch me work and provide recommendations and provide tips and tricks to speed up my work. If someone was simply walking around the office, watching people work and could advise on what someone had done when they had been working on a similar spreadsheet. Eventually I thought that if a computer had the intelligence it could carry out parts of my work.

It didn't seem like anyone else was working on systems like that, so that's what I wanted to create.

I started as an average Excel user, so I began to develop it for my initial needs. In speaking with others on the development of OfficeAutomata, I learnt what others deemed useful, some easy – wanting to automate minor things without learning how to program or resorting to macros, some more difficult – bank reconciliation, version control. It also emerged that having historical files of work completed and version control of files would have been very helpful for Excel files being passed around the office. Being able to see changes in files without locking people out of most functionality and remembering what I did last in a workbook also made my list and that’s why I built the officeAutomata as an audit trail prior to building the Cortana for Excel expansion.

Now, I am developing it more as a programmer and looking at the more complex ways it can be used but I feel I’ve fulfilled my initial aims for the regular user and I’d love to hear your feedback.

I left working with the Air Force in late 2013 and decided to pursue the idea more aggressively. I started to learn programming in January 2014 and the development of officeAutomata came from my own personal frustrations and my efforts to solve them in a user-friendly way. If you have any suggestions on functions you’d like to see, please let us know in the comments or contact us directly.

 

10 Uses for Cortana for Excel

A demonstration of a prototype of officeAutomata with Cortana

Hey everyone! Here's a brief demo of a new project I've been working on. Using Excel can be tedious and frustrating so I've created an easier way to communicate and automate parts of my own work, something I've dreamt of for a while.

This demo uses officeAutomata (for memory and actions) integrated with Windows Cortana (for voice controls). Below is a list of the main actions shown in the demo.

Let me know your thoughts in the comments below, and any suggestions for actions you would like Cortana to do for you.

The featured actions in the demo include:

1. Load Workbook

Loads any workbook in Cortana's memory, if you don't want to go searching for it.

2. Voice Control On/Off

Turns off and on the speech recognition, in case there are other conversations going on.

3. Keep Leading Zeros in Clipboard

Keeps all the leading zero's in a copy and paste, from any format; used for account numbers, phone numbers, etc.

4. Automatic Macro Recommendation

Auto-generates and recommends macros to assist you in workbook development based off your previous work.

5. Copy Visible Cells

Copies only the visible cells, ignores all hidden rows and columns.

6. Redo Copy

Redoes the last copy that has been canceled by Excel due to any input.

7. Finish Worksheet with Clipboard

Finishes a worksheet with the clipboard data based off any previous sheet in Cortana's memory.

8. Load History

Loads the memory file/audit trail of Cortana in Excel for review.

9. Jokes

Tells jokes about Excel (there are many).

10. Save and Close

Saves and closes the workbook.

 

Signup for Updates