DATA Step, Macro, Functions and more

NZX Financial Data Aggregation and Analysis

Reply
New Contributor
Posts: 4

NZX Financial Data Aggregation and Analysis

I have about 175 csv files of the same format as attached to this message. With each file I want to be able to read it into SAS and construct the following values and then save the data to a new outputted csv file.

 

1.     Normalised earnings per share = row 124/row 5 * 100

2.     Dividend payment per share per fiscal year (depends on the fiscal year of the firm, dates are shown in the file and for the attached file it is June 30 of each year ). Calculate the dividend payment = (interim dividend per share + the final dividend per share + any other dividend payments made between 1 July 19X0 and 30 June 19X1) That is the dividend per fiscal year spans two calendar year dates. See rows 167 and 171 for the interim and final dividends per share and rows 169 and 173 for the dates on which the dividend payments are made respectively.

3.     Dividend payout _NORM = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/Normalised earnings per share (from (1) above)

4.     Dividend payout_ADJ = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/adjusted earnings per share (row 170 on file)

5.     ROA = EBIT/TA = Row 104/Row 47

6.     ROA2 = EBITDA/TA = Row 101/Row 47

7.     ROA3 = NPAT/TA = Row 112/Row 47

8.     ROE = NPAT/TE = Row 112/Row 69

9.     Book value of debt = Total equity + liabilities – total equity = Row 88 – Row 69

10.                        Sales (= Turnover) = Row 95

11.                        Total shares = line 5

12.                        Total assets = line 47

13.                        Total equity = line 69

14.                        EBITDA = line 101

15.                        EBIT = line 104

16.                        NPAT = line 112

17.                        Normalised earnings = line 124

18.                        Interim Dividend in cents per share = line 132

19.                        Final dividend in cents per share = line 136

20.                        EPS basic (cents) = line 169

21.                        EPS adjusted (cents) = line 170

22.                        Adjusted share price = line 177

 

The output file needs to be formatted so that the variable labels are in the columns and the year variable is in the row. Currently, the data is formatted so the fiscal year end is in the column heading and the variable labels are in the rows.

New Contributor
Posts: 4

NZX Financials data aggregation and analysis

I have about 175 csv files of the same format as attached to this message. With each file I want to be able to read it into SAS and construct the following values and then save the data to a new outputted csv file.

 

1.     Normalised earnings per share = row 124/row 5 * 100

2.     Dividend payment per share per fiscal year (depends on the fiscal year of the firm, dates are shown in the file and for the attached file it is June 30 of each year ). Calculate the dividend payment = (interim dividend per share + the final dividend per share + any other dividend payments made between 1 July 19X0 and 30 June 19X1) That is the dividend per fiscal year spans two calendar year dates. See rows 167 and 171 for the interim and final dividends per share and rows 169 and 173 for the dates on which the dividend payments are made respectively.

3.     Dividend payout _NORM = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/Normalised earnings per share (from (1) above)

4.     Dividend payout_ADJ = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/adjusted earnings per share (row 170 on file)

5.     ROA = EBIT/TA = Row 104/Row 47

6.     ROA2 = EBITDA/TA = Row 101/Row 47

7.     ROA3 = NPAT/TA = Row 112/Row 47

8.     ROE = NPAT/TE = Row 112/Row 69

9.     Book value of debt = Total equity + liabilities – total equity = Row 88 – Row 69

10.                        Sales (= Turnover) = Row 95

11.                        Total shares = line 5

12.                        Total assets = line 47

13.                        Total equity = line 69

14.                        EBITDA = line 101

15.                        EBIT = line 104

16.                        NPAT = line 112

17.                        Normalised earnings = line 124

18.                        Interim Dividend in cents per share = line 132

19.                        Final dividend in cents per share = line 136

20.                        EPS basic (cents) = line 169

21.                        EPS adjusted (cents) = line 170

22.                        Adjusted share price = line 177

 

The output file needs to be formatted so that the variable labels are in the columns and the year variable is in the row. Currently, the data is formatted so the fiscal year end is in the column heading and the variable labels are in the rows.

Super User
Posts: 17,829

Re: NZX Financial Data Aggregation and Analysis

So what exactly is your question? 

 

New Contributor
Posts: 4

Re: NZX Financial Data Aggregation and Analysis

Sorry, I guess I didn't make it very clear. I have outlined all the processes that I will do at a later date. I am asking how to format the data so SAS understands it. So I have 175 files that I need to import and (from my understanding) transpose to then conduct the above analysis. How would I best go about this?
Super User
Posts: 17,829

Re: NZX Financial Data Aggregation and Analysis

You can import the files all at once assuming a consistent format. 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

 

Usually I say keep your data long, in this case transposing to a wide format may be easier with an array. 

Look at Proc Transpose. 

 

Your question is still pretty vague so high level answers are pretty much all we can offer. 

 

Once you have concrete questions you can get some more detailed answers. 

PROC Star
Posts: 1,093

Re: NZX Financial Data Aggregation and Analysis

To be honest, this forum is intended more for people who use SAS to ask questions about things they are having problems with or don't know how to do.

 

In the case of your post, you seem to be dropping a work unit, with the expectation that someone will develop the code to implement it at no charge.

 

I'll be happy to take this on for you at my usual hourly rate, but I don't expect that anyone will be interested in taking this on for free.

 

Tom

New Contributor
Posts: 4

Re: NZX Financial Data Aggregation and Analysis

How much would you charge to complete this process? I understand you work hourly but would you consider a detailing a quote for me?
PROC Star
Posts: 1,093

Re: NZX Financial Data Aggregation and Analysis

Let's take this offline...email me at tom.kari.consulting@bell.net

Super User
Posts: 10,500

Re: NZX Financial Data Aggregation and Analysis

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Ask a Question
Discussion stats
  • 8 replies
  • 766 views
  • 2 likes
  • 4 in conversation