Reformating an integer to date and creating a growth variable

[Falco Wolf]

Hello all,

The dataset below corresponds to the daily closing price during the period 2009 to 2019, of real estate investment trusts. I will want to compare the growth they have had per periods, 10 year, 5 year, 3 year and 1 year.

However, I have a couple of problems. Firstly, the date variable shows the dates as: 20090103. In Other words, 2009-01-03. 3rd of January 2009. Is there a way to reformat the integer into it forming a daily date that I can later use to create a time series?

The second thing, is once I have the graph, i will need to create a growth index it all to the year 2009 of the variable "ClosePrice".

by Country_Code (year), sort: gen growth_index = 1 if _n == 1
by Country_Code (year): replace index = index[_n-1] * (1+ClosePrice/100) if _n > 1

I think it might not work since there are weekends, where the closeprice is 0 that might need to be dropped (or ignored) I think this is correct. What is the best way to create a twoway scatterplot , which has more than 1 company graphed?

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date double ClosePrice str100 IQ_COMPANY_NAME str29 IQ_PRIMARY_SIC_INDUSTRY double IQ_PRIMARY_SIC_CODE str20 IQ_COUNTRY_NAME str2 IQ_COUNTRY_ISO_CODE long company_id
20090103     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090104     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090105 23.36 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090106 23.16 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090107  22.8 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090108 22.91 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090109 22.93 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090110     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090111     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090112 22.88 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090113 23.25 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090114 23.08 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090115 23.63 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090116 23.14 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090117     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090118     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090119     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090120 22.99 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090121 23.66 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090122  23.1 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090123 22.99 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090124     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090125     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090126 23.04 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090127 22.36 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090128 22.46 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090129 21.96 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090130 21.49 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090131     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090201     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090202 21.99 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090203 22.91 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090204 22.49 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090205 22.68 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090206 23.12 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090207     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090208     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090209 22.98 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090210  21.9 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090211 22.54 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090212 22.46 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090213 21.69 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090214     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090215     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090216     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090217 20.18 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090218 20.13 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090219 19.46 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090220 19.17 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090221     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090222     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090223 17.67 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090224 18.77 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090225 18.54 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090226 18.62 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090227 18.58 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090228     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090301     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090302 17.64 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090303    17 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090304 18.34 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090305 18.16 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090306 18.02 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090307     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090308     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090309 18.88 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090310 20.89 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090311 20.36 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090312 21.19 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090313 22.06 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090314     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090315     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090316  22.3 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090317 23.15 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090318 23.59 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090319 23.06 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090320 22.53 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090321     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090322     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090323 23.13 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090324 22.53 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090325  22.6 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090326  22.6 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090327 22.36 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090328     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090329     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090330 21.83 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090331 22.19 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090401 22.52 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090402    23 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090403 23.36 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090404     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090405     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090406 23.41 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090407  23.2 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090408 23.37 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090409 23.85 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090410     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090411     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
20090412     0 "W. P. Carey Inc." "Real estate investment trusts" 6798 "United States" "US" 24043
end

[Nick Cox]

Here's a hint on your date question:

. clear

. set obs 1
number of observations (_N) was 0, now 1

. gen long date = 20190408

. gen wanted = daily(string(date, "%8.0f"), "YMD")

. format wanted %td

. list

     +----------------------+
     |     date      wanted |
     |----------------------|
  1. | 20190408   08apr2019 |
     +----------------------+

[Falco Wolf]

Nick, Thanks. But How do I go from the "
08apr2019 to 8/04/2019? I imagine its in the format "%td", but can't find the solution. Also, I am trying to do a two way scatter plot, to create a time series, but All i get is graphs that look like crayon drawings. I'm not sure what I'm doing wrong.

[Nick Cox]

Already explained if you care to read and study the code!

I don't deal with this kind of data, so I have to make guesses here.

I can't comment on your graphics code or your graphs because you show neither. But with your example data (thanks!), firstly no graph makes much sense unless we're allowed to think that the zeros are really missing values (no trade?). If you look carefully you can spot that most of the zeros are at weekends. That does not require expertise: checking a few dates shows that they are Saturdays and Sundays, after which the pattern becomes clear. Presumably there are some extra public holidays too.

Even more fundamental if possible: Note that using your existing date variable is bound to produce bizarre results. For example, the jump from say 20071231 to 20080101, obvious enough to people reading it, is to Stata a jump of 8870. The jump from 20080131 to 20080201 is a jump of 70. Hence as a calendar year passes and we go from say 20071231 to 20081231 the total change is 10000 units (not 365 or 366 days!), and that is one big jump of 8870 at year end, several small jumps at each month end that are of order 70, and many tiny jumps of size 1, e.g. from 20070408 to 20070409.. Stata really does not know that the date variable you have is special.

So, as said, the first step is to convert your date to something Stata can make sense of. If you do this with your example data, you will find that the graph makes sense.

gen wanted = daily(string(date, "%8.0f"), "YMD")
format wanted %td

gen ClosePrice2 = ClosePrice if ClosePrice > 0

twoway connected ClosePrice2 wanted, xtitle("")

The next step is to work out if you need a business calendar:

help bcal

which other people may be able you with. I have never used them.