CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5
marcamillion
marcamillion

Reputation: 33765

Is there a simple way to calculate sumproduct in Rails?

I have a PositionGroup that has_many :positions.

Whenever a position_group object is touched, I would like to update pg.average_price like so:

# Average Price = ((position1.transaction_price * (position1.volume/total_volume) + 
# position2.transaction_price * (position2.volume/total_volme))

In my callback method, I tried this:

  def update_average_price
    total_volume = positions.sum(:volume)
    avg_price = positions.sum("transaction_price * (volume/#{total_volume})")
    update_column(:average_price, avg_price)
  end

But when I check the value of avg_price once multiple positions exist, I am getting 0.0.

This is my spec for this specific functionality:

it "should calculate the Weighted Average Purchase Price of all positions" do
  # Position 3: Price = 20, volume = 200
  # (Position 1 Price * (Position 1 Units/Total # of Units)) +
  # (Position 2 Price * (Position 2 Units/Total # of Units)) +
  # (Position 3 Price * (Position 3 Units/Total # of Units))
  # ($10 * (100/400)) + ($15 * (100/400) + $20 * (100/400)) =
  # ($2.50 + $3.75 + $5) = $11.25
  price3 = 20
  pos3 = create(:position, stock: stock, portfolio: portfolio, transaction_price: 20, current_price: price3, action: :buy, volume: 200, position_group: pg)
  expect(pg.average_price).to eql 11.25
end

This is the result when I run it:

1) PositionGroup methods should calculate the Weighted Average Purchase Price of all positions
     Failure/Error: expect(pg.average_price).to eql 11.25

       expected: 11.25
            got: 0.0

       (compared using eql?)

I am pretty sure the issue is this line, from my callback method update_average_price on PositionGroup:

avg_price = positions.sum("transaction_price * (volume/#{total_volume})")

Is there a better way to approach this or is there something else giving me that 0.0 when I shouldn't be?

Upvotes: 0

Views: 152

Answers (1)

avimoondra
avimoondra

Reputation: 896

avg_price = positions.sum("transaction_price * (volume/#{total_volume.to_f})")

to_f is missing, converting to float to get a decimal to work with.

Example

irb(main):022:0> Position.all
  Position Load (0.3ms)  SELECT "positions".* FROM "positions" LIMIT ?  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Position id: 1, transaction_price: 0.5e2, volume: 150, position_group_id: 1>, #<Position id: 2, transaction_price: 0.1e1, volume: 50, position_group_id: 1>]>
irb(main):023:0> Position.all.sum("transaction_price * (volume/#{total_volume.to_f})")
   (0.2ms)  SELECT SUM(transaction_price * (volume/200.0)) FROM "positions"
=> 37.75

Upvotes: 2

Related Questions