0

To track stocks I made an overview of bought and sold stocks, and generate a pivot table with the current amount of stocks owned. I want to calculate the total value of my stocks. The problem I have is the stocks are in different currencies, euros and dollars. In the pivot table I have the total number of stocks owned, and I can get the current value with: e.g.

=GOOGLEFINANCE(A5, "price")

To calculate the total value I can just multiply it with the total number of stocks in the pivot table. However as it is multiple currencies the prices are mixed. Is it possible to calculate the total value in the same currencies € when using a pivot table?

All the fields that I currently have: enter image description here

In the currency field I placed the symbol of the currency $ or €

Improve this question
5
  • 1
    Does this answer your question? How can I use GOOGLEFINANCE to return a share price in a certain currency?
    – Tedinoz
    Jun 16, 2021 at 2:36
  • @Tedinoz No, does that mean I have to check for each stock which currency it has and than add the calculation per row? Also my question involves a pivot table so i cant just say cellx * specific currency as when I update my list of stocks the order of the pivot table can change.
    – Sven van den Boogaart
    Jun 16, 2021 at 6:41
  • I think the idea would be to convert the currency before you create the pivot table. Regarding the number of currencies, your question suggests conversion to a common currency (euros?)- how else did you imagine that this would happen?
    – Tedinoz
    Jun 16, 2021 at 10:44
  • @Tedinoz I thought there might be a command to get the stock price in a specific currency, I found the convert to currency command and get stock price command. So I thought there might be a get stock in currency command
    – Sven van den Boogaart
    Jun 16, 2021 at 11:17
  • 1
    No, there doesn't appear to be a single stock price conversion command. I think the "currency" attribute (described here How can I use GOOGLEFINANCE to return a share price in a certain currency?) is as close as you get; then convert the rate and multiple by your source currency.
    – Tedinoz
    Jun 16, 2021 at 23:33

1 Answer 1

Reset to default
0

In currency field, you need to use the currency code euro=EUR, dollar=USD... Then I use my main currency with an IF condition. For this table:

=IF(E2="EUR";GOOGLEFINANCE(B2);GOOGLEFINANCE(B2))*GOOGLEFINANCE("Currency:"&E2&M$2))

M$2 is the cell where I put my main currency, change if necessary.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.