Categories
Excel

Connecting a Shape or WordArt to an Excel Cell

For connecting a shape to a cell in an Excel file, follow these steps.

Step 1

Insert a word art or shape

A sample WordArt in Excel

step 2:

Delete the text in the word art and type ‘=’ in the Excel formula bar. Then click a cell.

Placing a WordArt in an Excel Spreadsheet

step 3:

Now whatever you type in the cell, you will see it in shape or word art

bSuperior System as an Excel WordArt

 

As you see, it’s bSuperior System!

Categories
Excel

An example of Paste Special – Adding three zeros to numbers on Excel spreadsheet

Consider a column of Excel that contains cost values in terms of thousands of dollars but without zeroes like the image below.

A cost column in excel containing numbers in terms of thousands

How to convert these values to actual numbers? Like how to convert 13 to 13,000?

Without any Excel formula, you can multiple 1000 to all your costs with paste special. For this go as steps:

Step 1:

Type 1000 in a cell and copy. (Ctrl+c)

Copying in the cost column of the Excel file.

Step 2:

Select all your data (numbers) and go to Paste special (Ctrl+Alt+V). then do as the image below:

Doing paste special on the Excel cost column

At last you will have all the data with three zeros at the end.

A sample column of cost in an Excel file

Note:

This is not an Excel formula. If you want to undo this operation you should do as the same process but select Divide.

Categories
Excel

Making Organization Chart with Excel & Visio

Here we discuses about making Organization chart with visio by importing data from Excel sheet.

It is very simple and there is no need to use SmartArts or Shapes.

Step 1:

Prepare database in excel in this way:

The database sheet must have 2 columns. The first column is the person who reports and is labeled as “Owner”.  Second column is the person who gets the report and is labeled as “Report to” (See the figure below). Person A is the head of organization. B1 -B6 reports to A and so on.

figure 1

step 2:

Open Visio> open Organization template> select “Org Chart” tab> click Import. you will see’ll something like the figure below.

Note: the other steps will shown in figures step by step.

figure1

2

3

4

5

6

7

after clicking finish you will see the Organization chart.

8

Sample files can be downloaded from here:

Sampl File

 

 

Categories
Excel

My Activities on Excel Forums

Feel free to follow my activities on Excel forums.

Categories
Excel Tutorial

How to Make an XLSX File Macro Enabled in MS Excel 2015?

Please follow the steps below to Macro Enable an Excel file.

    • Open your XLSX Excel file.
    • Click on the “File” on the menu
      Excel Menu, File
    • Click “Save As”
      MS Excel, Save As
    • Choose your folder and select Excel Macro-Enabled Workbook (.xlsm) from the dropdown.
      Excel Macro-Enabled Workbook XLSM
    • Click “Save” button
    • Re-open you file
    • Click “Enable Content” on the yellow bar
      Enable Content on MS Excel
    • Now your file is Macro Enabled.
Categories
Excel Tutorial

Example of Looping through Worksheets, Deleting Rows & Converting to CSV

This VBA is an example of that loops through selected worksheets in a workbook, deletes the top ten rows and exports it as a CSV.

[vb]
Sub Test_loop()

ActiveWorkbook.Save

Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") – 1)

‘ Loop through all selected sheets.
For Each ws In ActiveWindow.SelectedSheets

IF ws.name <> "Monday" then
ws.Rows("1:10").Delete ‘ Delete 10 rows at top of each sheet.
ws.Copy

Range("A1").Interior.Color = 1 ‘ Format A1 so the top rows are included in the used range and saved
ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges = False

End IF
Next ws

End Sub

[/vb]

Categories
Excel

How to call the free Yahoo Finance API to get Stock Data (Excel & VBA)

Yahoo Finance API provides a simple way to download stock quotes. The service returns stock data in a CSV. This blog explain how to get stock data from Yahoo Finance using Excel & VBA. If you’re interested in a custom solution for your Excel file, please contact our Excel experts for a free quote.

In this example, we want to obtain the following Stock info of Google, Facebook and Microsoft.

  • Name of company
  • Last trade date
  • Last trade (price only)
  • Dividend Yield
  • P/E Ratio

Pre-req
Make sure Microsoft WinHTTP Services are enable. To do so , open Visual Basic Editor of your xlsm file. Click Tools >> References. And select Microsoft WinHTTP Services.

Demo

You can also download a demo of this Excel file.

Development Process – Step 1

Range A2 to be taken the head cell. Type “GOOG” in range A3, “FB” in A4 and “MSFT” in range A5. Type “n” in range B3, “d1” in range C3, “l1” in D3, “y” in E3 and “r” in F3. A sample shown below.

Excel VBA Yahoo Finance

Step 2

Open Microsoft Visual Basic for Applications on your Excel. Copy the code and paste it on your sheet’s VBA area.

Double click to select all.

[vb]
Option Explicit

‘ Example: Requesting Stock info of GOOG, FB and MSFT.

‘ In this example, we want to obtain the following Stock info
‘ – Name of company
‘ – Last trade date
‘ – Last trade (price only)
‘ – Dividend Yield
‘ – P/E Ratio

‘ Range A2 to be taken the head cell. Type "GOOG" in range A3,
‘ "FB" in A4 and "MSFT" in range A5. Type "n" in range B3, "d1"
‘ in range C3, "l1" in D3, "y" in E3 and "r" in F3.

‘ We use the following URL for getting Stock Quote from Yahoo
‘ URL: http://finance.yahoo.com/d/quotes.csv?s=FB+MSFT&f=snd1l1yr

‘ The CSV file data looks like the following

‘ "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
‘ "MSFT","Microsoft Corpora","9/4/2013",31.24,2.89,12.36

‘ The columns in CSV file is separated by comma. Split function
‘ is used to split the data on each line of CSV file at every
‘ occurrence of comma. However, some commas in the CSV file are
‘ part of a string, not separating the columns. For example
‘ consider the following line,
‘ "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
‘ The second comma is not a column separator. But it’s part
‘ of the company’s name "Facebook, Inc."
‘ This program will identify which comma is a column separator
‘ and which is part of a string

‘ ************
‘ Written by http://www.msexcelvancouver.com
‘ Thanks to http://www.youtube.com/watch?v=iSlBE3CWg5Q

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

Dim head As Range
Set head = Range("A2")

Dim i As Integer
Dim Symbols As String: Symbols = ""
Dim SpecialTags As String: SpecialTags = ""
Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://download.finance.yahoo.com/d/quotes.csv?s="
Dim rng As Range
Dim cell As Range

‘ Get the Stock Symbols
Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
For Each cell In rng ‘ Starting from a cell below the head cell till the last filled cell
Symbols = Symbols & cell.Value & "+"
Next cell
Symbols = Left(Symbols, Len(Symbols) – 1) ‘ Remove the last ‘+’

‘ Get the Special Tags
Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
For Each cell In rng ‘ Starting from a cell to the right of the head cell till the last filled cell
SpecialTags = SpecialTags & cell.Value
Next

‘ Put the desciption/name of each tag in the cell above it
Dim SpecialTagsArr() As String: Dim TagNamesArr() As String
Call Get_Special_Tags(SpecialTagsArr, TagNamesArr)
For Each cell In rng
cell.Offset(-1, 0).Value = FindTagName(cell.Value, SpecialTagsArr, TagNamesArr)
Next

Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags

Call Print_CSV(Yahoo_Finance_URL, head)

End Sub

‘ Download the CSV file and Print it in the cells
Sub Print_CSV(URL As String, head As Range)

‘ Get the CSV file from Yahoo Finance
Dim Http As New winhttprequest
Http.Open "GET", URL, False
Http.send

Dim Resp As String: Resp = Http.responsetext
Dim Lines As Variant: Lines = Split(Resp, vbLf)
Dim sLine As Variant
Dim Values As Variant
Dim Value As Variant

Dim i As Integer: i = 1
Dim next_column As Boolean: next_column = True
Dim col As Integer
Dim tmp As String

‘ Extract CSV file Data to Cells
For Each sLine In Lines
Values = Split(sLine, ",")
col = 1
tmp = ""
For Each Value In Values
‘ Read the info for this program which explains the lines below
tmp = tmp & Value
If Left(Value, 1) = """" And Right(Value, 1) <> """" Then next_column = False
If Not next_column And Right(Value, 1) = """" Then next_column = True
If next_column Then
head.Offset(i, col).Value = Replace(tmp, Chr(34), "") ‘ Remove ‘"’
tmp = ""
col = col + 1
End If
Next Value
i = i + 1
Next sLine
End Sub

‘ Find the name of the given tag
Function FindTagName(tag As String, SpecialTags() As String, Tag_Name() As String) As String
Dim pos As Integer
Dim tagName As String

On Error GoTo CannotFindTagName

pos = Application.Match(tag, SpecialTags, False) – 1

tagName = Tag_Name(pos)
FindTagName = tagName
Exit Function

CannotFindTagName:
MsgBox tag & " not found! Program terminates."
End
End Function
[/vb]

The following function includes two arrays, one containing the special tags and another containing tag names. You could use these arrays as a reference.

Double click to select all.

[vb]
‘ Yahoo Finance Special Tags & Names
Sub Get_Special_Tags(ByRef SpecialTags() As String, ByRef Tag_Name() As String)

ReDim SpecialTags(0 To 84) As String
ReDim Tag_Name(0 To 84) As String

SpecialTags(0) = "a": Tag_Name(0) = "Ask"
SpecialTags(1) = "a2": Tag_Name(1) = "Average Daily Volume"
SpecialTags(2) = "a5": Tag_Name(2) = "Ask Size"
SpecialTags(3) = "b": Tag_Name(3) = "Bid"
SpecialTags(4) = "b2": Tag_Name(4) = "Ask (Real-time)"
SpecialTags(5) = "b3": Tag_Name(5) = "Bid (Real-time)"
SpecialTags(6) = "b4": Tag_Name(6) = "Book Value"
SpecialTags(7) = "b6": Tag_Name(7) = "Bid Size"
SpecialTags(8) = "c": Tag_Name(8) = "Change & Percent Change"
SpecialTags(9) = "c1": Tag_Name(9) = "Change"
SpecialTags(10) = "c3": Tag_Name(10) = "Commission"
SpecialTags(11) = "c6": Tag_Name(11) = "Change (Real-time)"
SpecialTags(12) = "c8": Tag_Name(12) = "After Hours Change (Real-time)"
SpecialTags(13) = "d": Tag_Name(13) = "Dividend/Share"
SpecialTags(14) = "d1": Tag_Name(14) = "Last Trade Date"
SpecialTags(15) = "d2": Tag_Name(15) = "Trade Date"
SpecialTags(16) = "e": Tag_Name(16) = "Earnings/Share"
SpecialTags(17) = "e1": Tag_Name(17) = "Error Indication (returned for symbol changed / invalid)"
SpecialTags(18) = "e7": Tag_Name(18) = "EPS Estimate Current Year"
SpecialTags(19) = "e8": Tag_Name(19) = "EPS Estimate Next Year"
SpecialTags(20) = "e9": Tag_Name(20) = "EPS Estimate Next Quarter"
SpecialTags(21) = "f6": Tag_Name(21) = "Float Shares"
SpecialTags(22) = "g": Tag_Name(22) = "Day’s Low"
SpecialTags(23) = "h": Tag_Name(23) = "Day’s High"
SpecialTags(24) = "j": Tag_Name(24) = "52-week Low"
SpecialTags(25) = "k": Tag_Name(25) = "52-week High"
SpecialTags(26) = "g1": Tag_Name(26) = "Holdings Gain Percent"
SpecialTags(27) = "g3": Tag_Name(27) = "Annualized Gain"
SpecialTags(28) = "g4": Tag_Name(28) = "Holdings Gain"
SpecialTags(29) = "g5": Tag_Name(29) = "Holdings Gain Percent (Real-time)"
SpecialTags(30) = "g6": Tag_Name(30) = "Holdings Gain (Real-time)"
SpecialTags(31) = "i": Tag_Name(31) = "More Info"
SpecialTags(32) = "i5": Tag_Name(32) = "Order Book (Real-time)"
SpecialTags(33) = "j1": Tag_Name(33) = "Market Capitalization"
SpecialTags(34) = "j3": Tag_Name(34) = "Market Cap (Real-time)"
SpecialTags(35) = "j4": Tag_Name(35) = "EBITDA"
SpecialTags(36) = "j5": Tag_Name(36) = "Change From 52-week Low"
SpecialTags(37) = "j6": Tag_Name(37) = "Percent Change From 52-week Low"
SpecialTags(38) = "k1": Tag_Name(38) = "Last Trade (Real-time) With Time"
SpecialTags(39) = "k2": Tag_Name(39) = "Change Percent (Real-time)"
SpecialTags(40) = "k3": Tag_Name(40) = "Last Trade Size"
SpecialTags(41) = "k4": Tag_Name(41) = "Change From 52-week High"
SpecialTags(42) = "k5": Tag_Name(42) = "Percebt Change From 52-week High"
SpecialTags(43) = "l": Tag_Name(43) = "Last Trade (With Time)"
SpecialTags(44) = "l1": Tag_Name(44) = "Last Trade (Price Only)"
SpecialTags(45) = "l2": Tag_Name(45) = "High Limit"
SpecialTags(46) = "l3": Tag_Name(46) = "Low Limit"
SpecialTags(47) = "m": Tag_Name(47) = "Day’s Range"
SpecialTags(48) = "m2": Tag_Name(48) = "Day’s Range (Real-time)"
SpecialTags(49) = "m3": Tag_Name(49) = "50-day Moving Average"
SpecialTags(50) = "m4": Tag_Name(50) = "200-day Moving Average"
SpecialTags(51) = "m5": Tag_Name(51) = "Change From 200-day Moving Average"
SpecialTags(52) = "m6": Tag_Name(52) = "Percent Change From 200-day Moving Average"
SpecialTags(53) = "m7": Tag_Name(53) = "Change From 50-day Moving Average"
SpecialTags(54) = "m8": Tag_Name(54) = "Percent Change From 50-day Moving Average"
SpecialTags(55) = "n": Tag_Name(55) = "Name"
SpecialTags(56) = "n4": Tag_Name(56) = "Notes"
SpecialTags(57) = "o": Tag_Name(57) = "Open"
SpecialTags(58) = "p": Tag_Name(58) = "Previous Close"
SpecialTags(59) = "p1": Tag_Name(59) = "Price Paid"
SpecialTags(60) = "p2": Tag_Name(60) = "Change in Percent"
SpecialTags(61) = "p5": Tag_Name(61) = "Price/Sales"
SpecialTags(62) = "p6": Tag_Name(62) = "Price/Book"
SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date"
SpecialTags(64) = "r": Tag_Name(64) = "P/E Ratio"
SpecialTags(65) = "r1": Tag_Name(65) = "Dividend Pay Date"
SpecialTags(66) = "r2": Tag_Name(66) = "P/E Ratio (Real-time)"
SpecialTags(67) = "r5": Tag_Name(67) = "PEG Ratio"
SpecialTags(68) = "r6": Tag_Name(68) = "Price/EPS Estimate Current Year"
SpecialTags(69) = "r7": Tag_Name(69) = "Price/EPS Estimate Next Year"
SpecialTags(70) = "s": Tag_Name(70) = "Symbol"
SpecialTags(71) = "s1": Tag_Name(71) = "Shares Owned"
SpecialTags(72) = "s7": Tag_Name(72) = "Short Ratio"
SpecialTags(73) = "t1": Tag_Name(73) = "Last Trade Time"
SpecialTags(74) = "t6": Tag_Name(74) = "Trade Links"
SpecialTags(75) = "t7": Tag_Name(75) = "Ticker Trend"
SpecialTags(76) = "t8": Tag_Name(76) = "1 yr Target Price"
SpecialTags(77) = "v": Tag_Name(77) = "Volume"
SpecialTags(78) = "v1": Tag_Name(78) = "Holdings Value"
SpecialTags(79) = "v7": Tag_Name(79) = "Holdings Value (Real-time)"
SpecialTags(80) = "w": Tag_Name(80) = "52-week Range"
SpecialTags(81) = "w1": Tag_Name(81) = "Day’s Value Change"
SpecialTags(82) = "w4": Tag_Name(82) = "Day’s Value Change (Real-time)"
SpecialTags(83) = "x": Tag_Name(83) = "Stock Exchange"
SpecialTags(84) = "y": Tag_Name(84) = "Dividend Yield"

End Sub
[/vb]

YouTube Video

We also explained how to use this Excel file, on a YouTube Video.

Need some help?

Please feel free to contact our Excel experts if you are looking for some Excel help.

 

Categories
Excel

Welcome to MS Excel Vancouver Blog

Welcome to our blog. In this blog, we would write about Microsoft Excel such as tutorials, examples and some VBA coding! MS Excel Vancouver is a branch of bSuperior System Ltd. specialized in MS Excel and web technologies.
bSuperior System Ltd. Logo