XTDB Portfolio

This shows how the db at a point in time can comfortably enable account or portfolio analysis.

Before we get started, we'll start with a deps.edn that includes xtdb

{:deps
 {org.clojure/clojure {:mvn/version "1.10.3"}
  org.clojure/tools.deps.alpha
  {:git/url "https://github.com/clojure/tools.deps.alpha.git"
   :sha "e4fb92eef724fa39e29b39cc2b1a850567d490dd"}
  compliment/compliment {:mvn/version "0.3.11"}
  com.xtdb/xtdb-core {:mvn/version "1.23.0"}}}
Extensible Data Notation
(require '[xtdb.api :as xt])
12.4s

This starts a database, re-executing this cell will create a new empty portfolio.

(def ^xtdb.api.PXtdb system
  (xt/start-node {}))
8.6s

This creates a utility to view portfolios at 4 asof valuation dates

;; portfolios on a series of valuation dates
(defn hld-hist
  []
  (dorun
   (for [asof  [#inst "2019-01-10"
                #inst "2019-01-15"
                #inst "2019-03-14"
                #inst "2019-03-15"]]
     (println asof (xt/q (xt/db system asof)
                           '[:find ?t ?q
                             :where
                             [?port :name "My Trading Portfolio"]
                             [?port :holdings ?hldg]
                             [?hldg :quantity ?q]
                             [?hldg :asset ?a]
                             [?a    :ticker ?t]])))))
(hld-hist)
0.8s

Load up some data (asof 12/31/2018)

(->> [;; add 3 companies
      [::xt/put {:xt/id :company/t1, :name "IBM"}]
      [::xt/put {:xt/id :company/t2, :name "JP Morgan"}]
      [::xt/put {:xt/id :company/t3, :name "Ford"}]
      ;; add 3 assets based on those companies
      [::xt/put {:xt/id :security/t1,
                     :issuer :company/t1 :ticker "IBM",
                     :type "Equity"}]
      [::xt/put {:xt/id :security/t2, 
                     :issuer :company/t2 :ticker "JPM",
                     :type "Equity"}]
      [::xt/put {:xt/id :security/t3,
                     :issuer :company/t3 :ticker "F",
                     :type "Equity"}]
      ;; add 2 positions (portfolio unspecified)
      [::xt/put {:xt/id :pm/p1, :asset :security/t1, :quantity 100}]
      [::xt/put {:xt/id :pm/p2, :asset :security/t2, :quantity 200}]
      ;; add portfolio (with those 2 positions)
      [::xt/put {:xt/id :p/p1, 
                     :holdings [:pm/p1 :pm/p2],
                     :name "My Trading Portfolio"}]]
     ;; adding this asof date is optional
     (mapv #(conj %  #inst "2018-12-31"))
     ;; submit the transaction
     (xt/submit-tx  system)
     ;; get the tx time and wait before proceeding
     ::xt/tx-time
     ((fn wait [tx-time] (xt/sync system tx-time nil))))
(hld-hist)
0.8s
(->> [[::xt/put {:xt/id :pm/p1,
                     :asset :security/t1,
                     :quantity 350}
       #inst "2019-01-15"]]
     (xt/submit-tx system)
     :tx/tx-time
     (#(xt/sync system % nil )))
(hld-hist)
0.5s

This adds 1000 to position 1 (:pm/p1).

Although (hld-hist) may show the new shares but if you execute this in a separate repl it typically will not. This "submits" the transaction and then queries the db without waiting for the transaction to complete (e.g. kafka-log->crux-indexer) .

(let [asof  #inst "2019-02-14"
      position (xt/entity (xt/db system asof) :pm/p1)]
  (xt/submit-tx system
   [[::xt/put (update position :quantity + 1000) asof]]))
(hld-hist)
0.4s
(let [asof #inst "2019-03-15"
      port (xt/entity (xt/db system asof) :p/p1)]
  (->> [[::xt/put {:xt/id :pm/p3,
                       :asset :security/t3, 
                       :quantity 440}]
        [::xt/put (update port :holdings conj :pm/p3)]]
       ;; add asof date to each entry
       (mapv #(conj % asof))
       ;; submit transaction
       (xt/submit-tx system)))
(hld-hist)
0.4s
;; portfolios on a series of valuation dates
(hld-hist)
0.5s
0.0s

entity history

(->> (xt/entity-history (xt/db system) :p/p1 :asc {:with-docs? true})
  (map #(get-in % [:xtdb.api/doc :holdings])))
0.0s
0.1s

Note that the above shows two entries. The portfolio ':p/p1' only reflects the addition of a position, not the change to position :pm/p2 on "2019-01-15" where the "IBM" position was increased. e.g. no "nested history"

(xt/entity-history (xt/db system) :p/p1 :asc {:with-docs? true})
0.2s
(with-open [entity-hist (xt/open-entity-history (xt/db system) :p/p1 :asc {:with-docs? true})]
  (iterator-seq entity-hist))
0.1s

Logs

new version moved the log stuff .... need to re-find it

(doc xt/open-tx-log)
0.4s
(require '[clojure.reflect :refer [reflect]])
0.0s
(with-open [tx-log (xt/open-tx-log system 0 true)]
  (iterator-seq tx-log))
0.1s
0.0s
(defn hld-hist2
  []
   (for [asof  [#inst "2019-01-10"
                #inst "2019-01-15"
                #inst "2019-03-14"
                #inst "2019-03-15"]]
     [asof (xt/q (xt/db system asof)
                     '[:find (pull ?port [{:holdings [{:asset [:ticker]}:quantity]}])
                             :where
                             [?port :name "My Trading Portfolio"]
                       ])]))
(pprint (hld-hist2))
0.7s
(pprint
  (-> (hld-hist2)
    first
    second
    ffirst))
0.5s
0.0s
Runtimes (1)