Financial Spreadsheeting Week 1-12 Summary (Smt 2, 2023)

FINANCIAL SPREADSHEETING LONG SUMMARY FOR FINAL EXAM SEMESTER 2, 2023

Table of Contents MICROSOFT EXCEL’S SUMMARY (WEEK 1 – 6) ............................................................................................ 4 I. IMPORT DATA .................................................................................................................................. 4 1. Importing real-time stock data .................................................................................................... 4 2. Importing ASX companies’ stock historical data of from yahoo finance ...................................... 4 II. EXCEL FUNCTIONS ........................................................................................................................... 5 1. The NOW and TODAY Functions .................................................................................................. 5 2. The TEXT Function ....................................................................................................................... 6 3. The FORMULATEXT Function ....................................................................................................... 9 4. The SUM Function ..................................................................................................................... 10 5. The WORKDAY Function ............................................................................................................ 13 6. The WEEKDAY Function ............................................................................................................. 15 7. The DATEVALUE function ........................................................................................................... 16 8. The MATCH Function ................................................................................................................. 18 9. The OFFSET Function ................................................................................................................. 19 10. The SPILL Range Operator ..................................................................................................... 22 11. The INDIRECT Function .......................................................................................................... 23 12. The UNIQUE Function ........................................................................................................... 26 13. The ROW and COLUMN Functions ........................................................................................ 28 14. The IRR Function ................................................................................................................... 29 15. The NPV Function .................................................................................................................. 30 III. ASX TRADING DAYS ................................................................................................................... 35 1. Create an ASX trading day list .................................................................................................... 35 2. Derive a historic span from the ASX trading day list .................................................................. 37 IV. OTHERS ......................................................................................................................................... 40 1. Defining Names ......................................................................................................................... 40 2. Referencing in Cells ................................................................................................................... 45 3. Create a Chartsheet .................................................................................................................. 48 4. What If Analysis ......................................................................................................................... 49 5. Add Features to Quick Access Toolbar ....................................................................................... 50 VISUAL BASIC APPLICATION’S SUMMARY (WEEk 7 -12) ............................................................................ 53 I. Basics ............................................................................................................................................ 53 1. Definitions/Abbrevations .......................................................................................................... 53

MICROSOFT EXCEL’S SUMMARY (WEEK 1 – 6) I. IMPORT DATA 1. Importing real-time stock data 1) Ensure you are connected to internet. 2) Open an Excel workbook, and type any ASX code on the active cell (i.e. the cell you are highlighting). Example: type “WOW” (ASX code for Woolworths). 3) Go to “ data” tab, look for “Data types”, and click on the “stock” icon ( ). A dialog box will pop up on the right side of the excel workbook, showing all possible companies for “WOW”. 4) Select “Woolworths Group Limited”. 5) The “WOW” in the active cell will be replaced by “Woolworths Group Limited (XASX: WOW)”. 6) The following icon: will appear. Click on it and select any indicators to display them in real time manner: such as price, close price, and volume. 2. Importing ASX companies’ stock historical data of from yahoo finance 1) Open the web: au.finance.yahoo.com/quote/ inputASXcodehere ? .... 2) Click on “historical data” 3) Click on “time period” and set the time range (1D, 3D, 1M, 3M, 1Y, or custom) 4) Import the historical by downloading method or by query method as shown on the following tables: Step By downloading method By query method 1. On the “historical data” page, click on “download.” Right click on “download” on the “historical data” page .

Step By downloading method By query method 1. This will download the historical data as a csv file . Click on “Copy link address.” 2. Open the csv file with Microsoft excel. Open an excel workbook. 3. This will display the historical data table which consists of 7 columns : Date, Open, High, Low, Close, Adj. Close, and Volume. Click on “data” tab, look for the “Get & transform data” , and click on the “from web” icon ( ) . 4. The table is NOT automatically named. Paste the link address on the URL field and click “ Ok.” 5. Click Load if you want to display the data on a new worksheet. Click Load to if you want to display the data on an existing worksheet, and/or customize the output. 6. This will display the historical data table which consists of 7 columns : Date, Open, High, Low, Close, Adj. Close, and Volume. 7. The table is automatically named as “ the ASXcode. ” The range of the name will NOT include the titles of the columns. II. EXCEL FUNCTIONS 1. The NOW and TODAY Functions

2) Returns a long date value of a date: Please note that different letter’s numbers will generate different values ! For example, the A2 cell contains the serial number of 24 October 2023 . If you use TEXT Function on B2 cell to customised the date on A2, these are the possible results: No. Arguments of TEXT Functions Results 1 =TEXT(A2, ”d”) 24 2 =TEXT(A2, ”dd”) 24 3 =TEXT(A2, ”ddd”) Tue 4 =TEXT(A2, ”dddd”) Tuesday 5 =TEXT(A2, ”ddddd”) Tuesday 6 =TEXT(A2, ”m”) 10 7 =TEXT(A2, ”mm”) 10 8 =TEXT(A2, ”mmm”) Oct 9 =TEXT(A2, ”mmmm”) October

No. Arguments of TEXT Functions Results 10 =TEXT(A2, ”y”) 23 11 =TEXT(A2, ”yy”) 23 12 =TEXT(A2, ”yyy”) 2023 13 =TEXT(A2, ”yyyy”) 2023 3) Returns an ordinal number of a date by using combination of TEXT and VLOOKUP. Ordinal numbers are numbers with suffixes , such as 1st, 2nd ,3rd, and 4th. Example: Suppose you want to customise 24/10/ 2023 on B2 into 24th October 2023 on D2. The Arguments will be as follows: Important notes : a. You must add three double quotation marks (“””) before the “&” that intiates VLOOKUP and after the “&” that conclude the VLOOKUP. b. Carefully examine the table_array of the VLOOKUP function ! The table_array contains a manually inputted array instead of referencing to an array. It looks like this: {1,"st";2,"nd";3,"rd";4,"th";20,"th"} Manually inputted table-array has the following cahracteristics: (1) It begins and ends with curly bracket -> { }

2) FORMULATEXT function by using an array reference: 4. The SUM Function Returns the sum of a single cell with another cell, a single cell with an array, an array with an array, or all values in an array. Function : =SUM (number1, [number2], [number3], …) Where: Number1 : Can be a single cell,an array, or users can directly type a number in it. [number2] onwards : Same as number 1, but optional. Examples: 1) SUM a single cell with another cell :

2) SUM a single cell with an array : 3) SUM all values within an array : 4) SUM an array with another array :

5. The WORKDAY Function Returns the date of a working day after / before specific working days of another date. Function: =WORKDAY(start_date, days, [holidays]) Where: Start_date : fill in with the serial number of anchor date or reference that contains the anchor date Days : fill in with the number of days after the start date [holidays] : The square brackets indicate that this argument is optional. - Can be a single cell or an array that contains your customised dates of holidays Example : Suppose you typed Monday, 23 October 2023 on C2:

Then you want to know what is the working day date one working day after 23 October 2023. The function and the result will be: If you want to know what is the date of working day four working days after 23 October 2023, the function and the result will be: If you want to know what is the date of working day four working days after 23 October 2023 AND 24 October 2023 is holiday, the [holiday] argument must refer to the cell that contains 24 October 2023. the function and the result will be: If you want to know what is the date of working day four working days BEFORE 23 October 2023, simply add a minus before 4 on the days argument. The function and the result will be:

2) Return the sequencial numbers of dates in an array: 7. The DATEVALUE function Returns the serial number of a date/dates from the string of the date(s). Function: =DATEVALUE( date_text ) Where: Date_text : fill it with text string of the date(s) or with the A1 format of single cell or an array containing the text string(s) of the date(s). Examples: 1) Returns the serial number of a date from a single cell containing the string of the date :

2) Returns the serial number of a date by typing the string of the date directly on the function arguments dialog box: 42095 = 1 Apr 2015 3) Returns the serial number of dates from an array containing the strings of the dates :

1) MATCH Function if the lookup_array is a one-column array : 2) MATCH Function if the lookup_array is a one-row array : 9. The OFFSET Function Returns values of custom range based on a reference, given the number of rows and columns. Function: =OFFSET( reference, rows, cols, [height], [width]) Where: Reference : the reference from which you want to derive the offset. rows : the row by which the offset starts ( the row count starts from 0 , NOT 1 !).

cols : the column by which the offset starts (the column count starts from 0, NOT 1! ). [height] : The number of rows you want to display from the reference. • The square brackets indicate that It is optional. • If omitted/empty, the offset function will assume that you use the reference’s height. • The height count starts from 1 . “0” height will return a #REF! error. • A positive height will display values from the starting row downward. • A negative height will display values from the starting row upward. [width] : The number of columns you want to display from the reference. • The square brackets indicate that It is optional. • If omitted/empty, the offset function will assume that you use the reference’s width. • The width count starts from 1 . “0” width will return a #REF error. • Width can only be positive. It will display values from the starting column to the right. • Negative width, starting from -2 , will return a #REF! error. • -1 width is deemed as equal with 1 width Example : Suppose there is an array containing dates from 23 October 2023 to 31 October 2023 :