Sequential timeseries using CSV

Sequential timeseries using CSV preview image

1 collaborator

Default-person Doug Edmunds (Author)

Tags

csv 

Tagged by Doug Edmunds 10 months ago

timeseries 

Tagged by Doug Edmunds 10 months ago

Visible to everyone | Changeable by the author
Model was written in NetLogo 6.0 • Viewed 86 times • Downloaded 2 times • Run 0 times
Download the 'Sequential timeseries using CSV' modelDownload this modelEmbed this model

Do you have questions or comments about this model? Ask them here! (You'll first need to log in.)


WHAT IS IT?

This model processes a time series (sequential) CSV file. It uses a pointer method to advance through the CSV data. It allows you to restart at any point in the data.

It takes care of the mechanics of selecting the CSV, checking for end of file, etc. It provides a sample plot of the output as a line-chart and text output to screen, as well the ability to write to a logfile.

By inserting code in "specific" procedures you can use this as a skeleton on which to build a sequential model based on your CSV data.

It comes with a few sample CSV files of actual market transactions (high, low, open, close). Run those to get an idea of what you can do with it.

HOW IT WORKS

The program reads a CSV (comma separated values) file into a list-structured variable using csv:from-file. Each record of the CSV file is processed by the "go" and the "specifics-go" procedures. Charts and monitors show the sequential changes.

A 'data-pointer' is advanced on each cycle. The current row is stored in "vals-list". The item numbers of the vals-list (starting at item 0) are associated with variables. For example, if "Close" is the third field in the CSV file, then data-close is associated with item 2 of vals-list. The associations are set in the "specifics-get-next-record" procedure

HOW TO USE IT

To see how it works, press setup and select one of the example CSV files provided. These are simple ASCII files which you can open in a text editor.

Turn on the wait-switch to create a delay between each tick.

If you have a sequential data file, put it into the directory where this model is located. Press setup and select your data file. The program will attempt to load the first record, using the field order it finds in the "specifics-get-next-record" procedure. If your data will process without error, then you can make changes to examine other aspects of the time series data.

You can press the restart button to run the program again with reloading the data file. Try changing the sma slider value between runs.

To see how it works, load one of the example CSV files provided. If you want other data, go online and get time series data from Yahoo financial and other sources.

NOTE 1: MAKE SURE YOUR CSV FILE IS CLEAN

This code expects numerical values in numerical fields. It does not check for errors such as missing values or strings (such as NA). If there are errors, you may get a runtime error at that point in the data, or the title of the default chart will turn red.

NOTE 2: OLDEST ENTRIES MUST BE FIRST

The program requires that the OLDEST data is at the beginning of the CSV file. Some utility code is provided that you can use to reverse the order (reverse-csv-with-header and reverse-csv-no-header).

NOTE 3: IDENTIFY THE FIELDS

Each record (row) of the CSV has the same field structure. Modify the code in the "specifics-get-next-record" procedure to match your CSV. The default code is set to work with either 3 fields (date, close, volume) or 6 fields (date, high, low, open, close, volume) in that order. If your CSV has the same fields but in a different order, it is easier to change the item numbering in the code than to restructure your CSV.

ifelse vals-list-length = 3 [ ;; for tick data with 3 fields (Datetime Close Volume) set data-high-prev data-close set data-low-prev data-close set data-date item 0 vals-list set data-close item 1 vals-list set data-volume item 2 vals-list set data-high data-close set data-low data-close ][ ;; for financial data with 6 fields (Datetime High Low Open Close Volume) set data-high-prev data-high set data-low-prev data-low set data-date item 0 vals-list set data-high item 1 vals-list set data-low item 2 vals-list set data-open item 3 vals-list set data-close item 4 vals-list set data-volume item 5 vals-list ]

The field names (data-date, data-high, etc.) are globals. With a little revision, you can rename these fields to match up with the names of your fields. For example, if your CSV has a field called "size", as the third item, you could change the code to read

set data-size item 2 vals-list

You should make sure that each of the fields is assigned to a variable, even if you don't use that variable.

NOTE 4: USING DATA AVAILABLE ON THE INTERNET

You can easily re-order the data to match up with internet data.

As an example, get the data file of Apple stock trades from http://chartapi.finance.yahoo.com/instrument/1.0/AAPL/chartdata;type=quote;range=1d/csv

It will look something like this (see below). This looks like minute by minute data, with the oldest entries first.
THE APPLE STOCK DATA FROM YAHOO ON 8 FEB 2017 (just the start of it):

uri:/instrument/1.0/AAPL/chartdata;type=quote;range=1d/csv ticker:aapl Company-Name:Apple Inc. Exchange-Name:NMS unit:MIN timezone:EST currency:USD gmtoffset:-18000 previous_close:131.5300 Timestamp:1486564200,1486587600 labels:1486566000,1486569600,1486573200,1486576800,1486580400,1486584000,1486587600 values:Timestamp,close,high,low,open,volume close:131.2700,132.2100 high:131.3110,132.2200 low:131.2200,132.1700 open:131.2500,132.2060 volume:0,1526743552 1486564258,131.3328,131.3500,131.2200,131.2500,698100 1486564319,131.3800,131.4000,131.3100,131.3300,281000 1486564322,131.4800,131.5400,131.3600,131.4000,134300 1486564380,131.5900,131.6600,131.5000,131.5300,178400 ... ...(about 400 rows of data)

Find the parts needed for a csv file a) the header (which is on the line starting with "values" and b) the data. Delete the other lines. Save this to a csv file (unformatted text file).

Timestamp,close,high,low,open,volume 1486564258,131.3328,131.3500,131.2200,131.2500,698100 1486564319,131.3800,131.4000,131.3100,131.3300,281000 1486564322,131.4800,131.5400,131.3600,131.4000,134300 1486564380,131.5900,131.6600,131.5000,131.5300,178400 ...

The header indicates that the there are 6 fields, and the order of the fields is datetime-close-high-low-open-volume. To make this work, modify the Code tab procedure "specifics-get-next-record". Change the numbers of the items to point to the right variables. The Yahoo data order is set to look like this:

;; for financial data with 6 fields (Datetime High Low Open Close Volume) set data-high-prev data-high set data-low-prev data-low set data-date item 0 vals-list set data-high item 2 vals-list ;; was 1 set data-low item 3 vals-list ;; was 2 set data-open item 4 vals-list ;; was 3 set data-close item 1 vals-list ;; was 4 set data-volume item 5 vals-list

That's all the code change you need to make!

NOTE 5: LOG FILE AND SCREEN OUTPUT

You can maintain a written log of whatever you want to track. The log file is a basic ascii text file. Pressing the delete logfile button will remove it, with no backup.

CREDITS AND REFERENCES

Author: Doug Edmunds Feb 2017

Comments and Questions

Please start the discussion about this model! (You'll first need to log in.)

Click to Run Model

;; TimeSeries-core.nlogo v1.5
;; author: Doug Edmunds 24 Feb 2017 dougedmundsATgmailDOTcom
;; requires ver 6.0+

extensions [ csv ]

globals [
  data-source
  data
  vals-list vals-list-length
  data-pointer data-length
  logfile
  ;; The next globals are the names given to the fields in the CSV
  ;; If not appropriate names for your csv fields, change them here and
  ;; in the specifics-get-next-record procedure.  As a suggestion,
  ;; comment out the next line temporarily, to see all the places
  ;; where these globals are used in the code.
  data-date data-high data-low  data-open data-close data-volume

;; add new variables here
  ;; For calc-sma procedure (calculate simple moving average)
  ;; included just for demonstration purposes
  window-list   ;; a list (initially empty) of values used to calculate the average
  window-length ;; numeric, used to size the window-list
  ;; window-length gets value from sma-slider
  sma ;; simple moving average, numeric
]

;; interface globals
;; wait-switch?       boolean
;; wait-delay-slider  slider
;; print-to-logfile?  boolean
;; print-to-output?   boolean
;; chart-data         plot name

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

to setup
  ca
  ;; to hide "world", uncomment the next line
    ; set-patch-size -1

  ;;set data-pointer -1
  set data-length 0

  ;; alternative A
  ;; use this to select a file from the directory
  set data read-data user-file
  ;; alternative B
  ;; to hardcode the data source, use this code instead
    ;   set data-source "my_cvs_file.cvs"
    ;   set data read-data data-source

  ;; create a logfile
  set logfile "logfile.txt"
  let log-entry word "\r -- New Session -- " date-and-time
  if print-to-logfile? [
    print-to-file logfile log-entry
    print-to-file logfile logfile
    print-to-file logfile data-source ]
  if print-to-logfile? [ output-print logfile]
  output-print log-entry

ifelse empty? data
    [ output-print word date-and-time " - no file selected"]
    [ output-print data-source

      ;; if you have problems with headers, then make changes here
      ;; this code reads the first line; if last item is a string,
      ;; it assumes it is a header, and removes it
      if is-string? last first data
         [set data butfirst data]

      set data-pointer 0
      set data-length length data
      set vals-list first data
      ;; show the length of first entry in observer area
      type "the number of fields in each record is "
      print length vals-list
      print vals-list

      ;; use length to determine if tick data or not
      set vals-list-length length vals-list
      ;; specifics-get-next-record is called once at startup
      ;; this is done to establish a y range for the chart-data plot
      specifics-get-next-record
      clear-plots
      specifics-setup
      specifics-logfile
      ;; only reset if a file is selected. Ties in with disabled go buttons
      reset-ticks ]
end 

to specifics-setup
;; for calc-sma demonstration purposes, these global variables are initialized
  ;; calc-sma uses data-close. The first record of the CSV has been read,
  ;; so we use data-close here to initialize sma-average
  set window-list [ ]
  set window-length  sma-slider ;;NOTE, only set at start or restart
  set sma data-close
  calc-sma
end 

;;data-pointer must be incremented
;;  before calling get-next-record

to go
  ;; first check if should stop
  ;; A. prevent runtime error if setup was not run first
  if data-length = -1 [output-print "no file selected" stop]
  ;; B. prevent runtime error if at end of file or no file selected
  if data-pointer >= data-length [output-print "end of file [1]" stop]
  ;; C. stop the stream if at end of file after increment
  set data-pointer data-pointer + 1
  if data-pointer >= data-length[ output-print "end of file [2]" stop]
  ;; not at end of file, so continue
  get-next-record
  if wait-switch? [ wait wait-delay-slider]
  specifics-go
  specifics-logfile
  tick
end 

to specifics-go
  ;;add strategy code here
  calc-sma
end 

;; sample logfile details
;; modify this section to print whatever details you want.

to specifics-logfile
  if print-to-logfile?
    [ print-to-file logfile (word "data-close " data-close) ]
  if print-to-output? [
    output-print vals-list
    output-print (word "data-close " data-close) ]
end 

to get-next-record
  set vals-list item data-pointer data
  specifics-get-next-record
end 

to specifics-get-next-record
  ;; variables that needs to be updated before
  ;; the current record is changed are updated here
  ;; for example, set data-close-previous  data-close

  ;; MODIFY THIS TO MATCH UP WITH CSV
  ;; the specific code here is a model to handle
  ;; data with a record length of 3 or 6.
  ;; You should modify this section as needed to
  ;; match up with the field structure of your CSV

  ifelse vals-list-length = 3 [
  ;;  for tick data with 3 fields  (order: Datetime Close Volume)
  set data-date   item 0 vals-list
  set data-close  item 1 vals-list
  set data-volume item 2 vals-list
  set data-open   data-close
  set data-high   data-close
  set data-low    data-close
  ][
  ;;  for financial data with 6 fields (order: Datetime High Low Open Close Volume)
  set data-date   item 0 vals-list
  set data-high   item 1 vals-list
  set data-low    item 2 vals-list
  set data-open   item 3 vals-list
  set data-close  item 4 vals-list
  set data-volume item 5 vals-list
  ]
end 

to-report read-data [ file-name ]
;; create rows as an empty list
  let rows []
;; user-file reports false if no file is selected
;; if file-name is not a csv file, expect a run-time error.
  if file-name != false [
    set data-source file-name
    file-open file-name
 ;; csv:from-file creates a list of lists
    set rows csv:from-file file-name
    file-close ]
  report rows
end 

to print-to-file [file-name file-text]
  file-open file-name
    file-print file-text
    file-close
end 

to delete-logfile
  if logfile != 0 and file-exists? logfile [file-delete logfile]
end 

to clear-plots
  ;;clear-all-plots
  ;; alternatively you can set and clear plots one by run and reset the ranges
   set-current-plot "chart-data"
   clear-plot
   set-plot-y-range  precision (data-close - 0.01) 4 precision (data-close + 0.01) 4
end 

to restart-data
  ;; similar to setup, but doesn't load data from file
  set data-pointer 0
  set data-length length data
  set vals-list first data
  specifics-get-next-record

output-print "Reset to first record"
  output-print vals-list

  let log-entry word "\r -- New Session -- " date-and-time
  if print-to-logfile? [
    print-to-file logfile log-entry
    print-to-file logfile logfile
    print-to-file logfile data-source ]
  specifics-setup
  specifics-logfile
  clear-plots
  reset-ticks
end 

;;;;;;;; UTILITIES ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


;; If your csv file stores the newest records first, use one of the
;; procedures below to reverse the order of the records.
;; Type the procedure name on the observer line
;;  FIRST, inspect the csv in a text editor
;;   If the csv has a header, you must use 'reverse-csv-with-header'
;;   If the csv does not have a header, you must use 'reverse-csv-no-header'
;;  Note: this procedure overwrites 'reversed.csv' if it already exists
;;  It does not change the original file.

to reverse-csv-with-header
  set data read-data user-file
  ;remove the header and put it at the end
  let header first data
  set data butfirst data
  set data lput header data
  set data reverse data
    ; save the data.  Manually rename the file when done
  csv:to-file "reversed.csv" data
  set data []
  output-print "Done - changes have been saved to reversed.csv"
  output-print "Inspect new file before using it."
end 

to reverse-csv-no-header
  set data read-data user-file
  set data reverse data
    ; save the data.  Manually rename the file when done
  csv:to-file "reversed.csv" data
  set data []
  output-print "Done - changes have been saved to reversed.csv"
  output-print "Inspect new file before using it."
end 

;;;;;;;;; NEW CODE ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;put all new code below here

;; calculate a simple moving average on a moving window of data

to calc-sma
  ifelse length window-list < window-length
    [ set window-list fput data-close window-list ]
    [ set window-list but-last window-list
      set window-list fput data-close window-list ]
  set sma mean window-list
end 

There is only one version of this model, created 10 months ago by Doug Edmunds.

Attached files

File Type Description Last updated
Sequential timeseries using CSV.png preview Preview for 'Sequential timeseries using CSV' 10 months ago, by Doug Edmunds Download
TestData-Header-Daily.csv data Test data with a header row 10 months ago, by Doug Edmunds Download
TestData-NoHeader.csv data Test data with no header row 10 months ago, by Doug Edmunds Download

This model does not have any ancestors.

This model does not have any descendants.