Excel – Data from Web

Many a times in office hours, there will be some interesting data over the internet to follow in live like stock prices, election counting results, and importantly live cricket score cards. Though my manager do not minds me checking the score regularly through browser, opening a browser draws unwanted attention on to another related news article / tweets / photo …etc. thus losing track on the work I am doing in my Excel sheet. (there are lot of psychological / behavioral studies performed on how web browsing changes individual working styles which are beyond my writing skills to put them here)

To avoid this navigating across web browser and Excel, I decided to link the score card to one of working Excel-sheet or some corner working Excel-sheet. One of the easiest way to do this is link the web connection data to Excel. Though there are many other ways (defining connections, VBA), I will narrate my own of doing it. Here, I am taking the example of IPO subscription live data instead of interesting cricket score. My friends who is cricket enthusiasts, will do it easily once they follow this example.

Regarding the IPO subscription, it is “MONTE CARLO FASHIONS LIMITED” subscription which is live as of today (issue period: 3rd to 5th Dec 2014) through book running. Though I am not a vivid investor, I do keep a track on the equity markets of India so I am just curious on how the subscription of retail investors changes over the issue period. The data is available at the link http://www.bseindia.com/markets/publicIssues/CummDemandSchedule.aspx?ID=890.

URL Link Header

URL Link Excel Data

Now, to get this data into excel sheet, 1>click on “From Web” in the data, 2>enter the URL from where data needs to be captured as shown in the below image

Excel Web Data Link

Here the Excel automatically highlights table with data as shown highlighted yellow boxes with black arrow. So select the table from where the target data is available. Once click on import, Excel prompts where to import the data. Here Excel gives many options, we can just select any of the cell / new worksheet. Once the data is fetched into the sheet, we can format to our liking. By default, Excel keeps the auto update option to true. But care has to be taken to not to overwrite the format upon update.

Excel Web Data Live Score Shares

That’s it. All now we have to do is for our favorite cricket score card… Easy. Now, you see the cricket scores in Excel.

Posted in Technology | Tagged , , , , , , , | 3 Comments

Excel – Find the last word

On one of these days, I have received a following text from one of my friend who scribbled his various expenses in notepad and asked me help me tabulating his expenses.

Rent 4,000
Tuition Fees 8,000
Canteen Food Expenses 2,000
Road trip to Munich 1,100
News Media Yearly Subscription 500
Birthday Party Expenses 600

To tabulate the data, I copy pasted the data into my Excel workbook. Now I need to segregate the given data into expense head and expense value. The only pattern I could find is the expense value is the last word in the texts given.

My initial thought is to run the text to columns wizard and get the expenses easily. But the expense heads are of different word lengths resulting the expenses in different columns. Though there are other ways to solve this problem (Array functions, REPT function to make some pattern, Replace with escape sequence …etc.), I thought there should be a easy way out to get last word of the expense.

After recollecting all the text functions in Excel, I thought simple “Right” function will solve the problem. But the expense values are of different digits which is constraining the “Right” function to give the desired result.

The only pattern I could find is the characters after the last space is my expense values. Now how do I locate the last space and how do I find how many spaces are there in each expense.

To find how many words / spaces in the string, replace all the spaces in text and difference between new string and initial string shall give the number of words / spaces. Here we are using Substitute function to substitute all the spaces in the string.

Word Count

To find the last word / space, we will use the Substitute function again but just to substitute the last space by inputting the number of spaces as instance number. We can use any delimiter to find its location as the substitute character. Here I am using $ character as a delimiter.

Last word delimiter

Once we have the delimiter set at just before the last word, the desired task of getting the last word is achieved. Now we just need to find the delimiter and get the text from there to till last characters. We use Find, Len, Mid Excel functions to perform this task. Find function is used to find the delimiter character, Mid function is used to get the text from start of delimiter to till the last character of the string. Difference of location of delimiter character and length of string will the remaining characters in the string i.e. number of digits in the expense.

=MID(A1,FIND("$",D1)+1,LEN(A1)-FIND("$",D1))

Last word of string Excel

Now we have the last word of each expense, which is our expense. Expense heads can be obtained by taking the left portion of delimiter location.

=LEFT(A1,FIND("$",D1))

Finally we have segregated the given text into its expense heads and expense values.

You can download the excel workbook here Last Word.

Posted in Technology | Tagged , , | 2 Comments

Microsoft Excel Auto Suggest

The development Ajax has made feasible to get the information from server without reloading the web page. If it is technological development, Google has created a human advancement in how we search for the things in the form of habituating us to Auto Suggest feature while searching. As Nicholas Carr may say it as, manipulation of what we intended to search not just altering about our thinking process. But we search for Excel code, when you need most of it. So lets get to the business, leaving the psychology to the researchers out there for their doctoral thesis.

Excel Auto Suggest Word VB

Our objective is to show the list of one column entries of tabular data based on the search string user inputs.

As we can not pop up the inner HTML like the auto suggest of Google, we will create a text box and list box to show the probable suggestions. The same can be accomplished with just the native Excel cells. So we create a Textbox named TextBox1 and Listbox named ListBox1. (Developer tab -> Insert ->ActiveX Controls). If you want user to select the entries in auto suggest list box and work on the selected entry, we can link the list box to cell in worksheet so that the necessary operations can be performed.

We need to trigger the auto suggest when the user enters / changes some of text box, so we will write our application code in the change event of text box.

'this code has to be in object' located sheet. Can be Sheet1 or whatever name
Private Sub TextBox1_Change()
    'get the value of user input
    textinput = Sheet1.TextBox1.Value
    'replace 1 in the Mod operator for how many characters to trigger the macro
    'also Len(textinput) > 3 shall optimize
    If Len(textinput) Mod 1 = 0 Then
        'MsgBox "Event triggered"
        'call the macro
        Macro1
    End If
End Sub
The following code uses the Find feature of excel and loop through the results to update the listbox
'this code has to be one of the module
Sub Macro1()
    'clear the list box of previous entries
    Sheet1.ListBox1.Clear
    'select the range of column entries of parent table
    'naming of the range can also be used
    'here my data is in B1:B717 of sheet2
    With Worksheets("Sheet2").Range("B1:B717")
        'variable to keep the search items
        Dim findResult As Range
        'use the builtin find functionality of excel
        'looping through the cells can also do the same job
        'my search input is in Sheet1.TextBox1.Value
        Set findResult = .Find(What:=Trim(Sheet1.TextBox1.Value), LookIn:=xlValues, LookAt:=xlPart)
        If Not findResult Is Nothing Then
            'store the fisrt result of find to stop the loop as nextresult variable of last result will yield first result
            Dim firstFound As String
            firstFound = findResult.Address
            'loop to add to list box
            Do
                'keep adding the find results to listbox
                Sheet1.ListBox1.AddItem (findResult)
                Set findResult = .FindNext(findResult)
            'loop until we came back to first address
            Loop While findResult.Address <> firstFound
        End If
    'not necessary to use with the statement
    End With
End Sub

The given code is not optimized and intended for Excel novice users like me. This program is working without any delay for 30,000 rows of data on i5 processor machine with 4GB RAM.

One optimizing way is trigger the search event after initial 3 characters, otherwise if users enter “a” may yield all the entries in the data.

Another feature that can be implemented is use of regular expression with wild characters in the search string, and multiple word search. For example, if users enters Flat Round and expecting Rounded Flat Bars, Flatten Rounded Coil in the results along with Flat Round Sheets, then in the find search input shall be of “*Flat*Round*” and “*Round*Flat*”. This is one simple example of using wild characters. Usage of escape sequencing will give more possible implementations.

Posted in Technology | Tagged , , , , | 4 Comments