Elasticsearch for Stocks

Store the BSE/NSE daily stock feeds in Elasticsearch for research.

Create Elasticsearch Index

PUT stock-index
{
  "settings": {
    "index": {
      "number_of_shards": 1,
      "number_of_replicas": 2,
      "analysis": {
        "normalizer": {
          "lowercaseNormalizer": {
            "type": "custom",
            "filter": [
              "lowercase",
              "asciifolding"
            ]
          }
        }
      }
    }
  },
  "mappings": {
    "dynamic": "false",
    "properties": {
      "Symbol": {
        "type": "keyword",
        "normalizer": "lowercaseNormalizer"
      },
      "CreatedDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      },
      "Open": {
        "type": "double"
      },
      "High": {
        "type": "double"
      },
      "Low": {
        "type": "double"
      },
      "Close": {
        "type": "double"
      },
      "Volume": {
        "type": "double"
      },
      "FeedProvider": {
        "type": "text"
      }
    }
  }
}   
  
       

Adding sample data/records

        
#Add first Stock Document in elasticsearch 

PUT /stock-index/_doc/1
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-08",
    "Close": 389.6,
    "Open": 388.45,
    "High": 393.4,
    "Low": 386.05,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/2
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-09",
    "Close": 387.6,
    "Open": 393.45,
    "High": 394.9,
    "Low": 382.25,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/3
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-10",
    "Close": 387.7,
    "Open": 391.1,
    "High": 393,
    "Low": 385.7,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/4
{
  "Symbol": "SBI",
 "CreatedDate": "2021-03-12",
    "Close": 381.1,
    "Open": 391.7,
    "High": 393.2,
    "Low": 377.5,
  "FeedProvider": "zerodha"
}


PUT /stock-index/_doc/5
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-15",
    "Close": 383.9,
    "Open": 382.5,
    "High": 386,
    "Low": 369.35,
  "FeedProvider": "zerodha"
}


PUT /stock-index/_doc/6
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-16",
    "Close": 378.55,
    "Open": 385.6,
    "High": 387.85,
    "Low": 376.1,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/7
{
  "Symbol": "SBI",
   "CreatedDate": "2021-03-17",
    "Close": 368.15,
    "Open": 378.2,
    "High": 379.65,
    "Low": 366.05,
  "FeedProvider": "zerodha"
}


PUT /stock-index/_doc/8
{
  "Symbol": "SBI",
   "CreatedDate": "2021-03-18",
    "Close": 367.1,
    "Open": 371.5,
    "High": 375,
    "Low": 361.1,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/9
{
  "Symbol": "SBI",
 "CreatedDate": "2021-03-19",
    "Close": 371.15,
    "Open": 363.95,
    "High": 372.9,
    "Low": 357.25,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/10
{
  "Symbol": "SBI",
   "CreatedDate": "2021-03-22",
    "Close": 367,
    "Open": 372,
    "High": 372.8,
    "Low": 363.5,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/11
{
  "Symbol": "SBI",
   "CreatedDate": "2021-03-23",
    "Close": 372.7,
    "Open": 368.7,
    "High": 377.95,
    "Low": 367.05,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/12
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-24",
    "Close": 359.85,
    "Open": 368.5,
    "High": 369.05,
    "Low": 358.65,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/13
{
  "Symbol": "SBI",
   "CreatedDate": "2021-03-25",
    "Close": 355.2,
    "Open": 360,
    "High": 360.85,
    "Low": 345.2,
  "FeedProvider": "zerodha"
}

PUT /stock-index/_doc/14
{
  "Symbol": "SBI",
  "CreatedDate": "2021-03-26",
    "Close": 356.4,
    "Open": 360,
    "High": 361.8,
    "Low": 354.95,
  "FeedProvider": "zerodha"
}


GET /stock-index/_search
{
  "query": {
    "match": {
      "Symbol": "SBI"
    }
  }
}
        

View data/regcords

GET /stock-index/_search

Searching .

Search a particular stock query or field as belows

#Try 1
GET /stock-index/_search
{
  "query": {
    "match": {
      "Symbol": "SBI"
    }
  }
}

#Try
GET /stock-index/_search
{
   "query": {
     "range": {
       "Close": {
         "gte": 100,
         "lte": 389
       }
     }
   }
}

#Try
GET /stock-index/_search
{
   "query": {
     "range": {
       "CreatedDate": {
         "gte": "2021-03-08",
         "lte": "2021-03-09",
          "format": "yyyy-MM-dd"
       }
     }
   }
}

#Try
GET /stock-index/_search
{
  "size": 10,
  "query": {
    "range": {
      "CreatedDate": {
        "gte": "now-90d/d",
        "lte": "now/d",
        "format": "yyyy-MM-dd"
      }
    }
  }
}


#Simple moving avg
GET /stock-index/_search
{
  "size": 0,
  "aggs": {
    "hourly_data": {
      "date_histogram": {
        "field": "CreatedDate",
        "interval": "day"
      },
      "aggs": {
        "stock_value": {
          "sum": {
            "field": "Close"
          }
        },
        "mva_demo": {
          "moving_avg": {
            "buckets_path": "stock_value",
            "window": 5,
            "model": "simple"
          }
        }
      }
    }
  }
}
 


#Linear Moving Average
GET /stock-index/_search
{
  "size": 0,
  "aggs": {
    "hourly_data": {
      "date_histogram": {
        "field": "CreatedDate",
        "interval": "day"
      },
      "aggs": {
        "stock_value": {
          "sum": {
            "field": "Close"
          }
        },
        "mva_simple": {
          "moving_avg": {
            "buckets_path": "stock_value",
            "window": 10,
            "model": "simple"
          }
        },
        "mva_linear": {
          "moving_avg": {
            "buckets_path": "stock_value",
            "window": 10,
            "model": "linear"
          }
        }
      }
    }
  }
}


#Exponentially Weighted Moving Average
GET /stock-index/_search
{
  "size": 0,
  "aggs": {
    "hourly_data": {
      "date_histogram": {
        "field": "CreatedDate",
        "interval": "day"
      },
      "aggs": {
        "stock_value": {
          "sum": {
            "field": "Close"
          }
        },
        "mva_simple": {
          "moving_avg": {
            "buckets_path": "stock_value",
            "window": 10,
            "model": "simple"
          }
        },
        "mva_linear": {
          "moving_avg": {
            "buckets_path": "stock_value",
            "window": 10,
            "model": "linear"
          }
        },
        "mva_ewma": {
          "moving_avg": {
            "buckets_path": "stock_value",
            "window": 10,
            "model": "ewma",
            "settings": {
              "alpha": 0.3
            }
          }
        }
      }
    }
  }
}

 #minimum of the price monthly
 GET /stock-index/_search
 {
  "size": 0,
  "aggs": {
    "price_per_month": {
      "date_histogram": {
        "field": "CreatedDate",
        "calendar_interval": "month"
      },
      "aggs": {
        "price": {
          "min": {
            "field": "Close"
          }
        }
      }
    },
    "min_monthly_price": {
      "min_bucket": {
        "buckets_path": "price_per_month>price" 
      }
    }
  }
}


#Moving Min 
GET /stock-index/_search
{
  "size": 0,
  "aggs": {
    "day_data": {
      "date_histogram": {
        "field": "CreatedDate",
        "interval": "day"
      },
      "aggs": {
        "stock_value": {
          "sum": {
            "field": "Close"
          }
        },
         "the_movfn": {
          "moving_fn": {
            "buckets_path": "stock_value",  
            "window": 10,
            "script": "MovingFunctions.min(values)"
          }
        }
      }
    }
  }
}
       

Reference

• Documentation: BSE
• Aggs: ES search-aggregations