[GNC] Enable Online Stock Price Updating in GnuCash

Thu Jun 10 21:53:13 EDT 2021

PS - attached is the GnuCash file that I created - In case you want to open it to see what I did with the Stock Shorting

From: Jai Rao
Sent: Thursday, June 10, 2021 8:47 PM
To: Geoff <cleanoutmyshed at gmail.com>; gnucash-user at gnucash.org; Christopher Lam <christopher.lck at gmail.com>
Subject: RE: Enable Online Stock Price Updating in GnuCash

Hello All,

Side note:  I saw the following link for some guidance on Shorting Options - in case it is of any use.

  *   https://www.bogleheads.org/forum/viewtopic.php?t=188428

I've gotten to make Short Selling a Stock to work (at least in a way that makes some sense to me)

  *   Hopefully the following makes some sense to others

I established the following (mock) accounts:

  *   [cid:image001.png at 01D75E3A.9EA64590]
     *   In Assets I have a Brokerage
        *   Brokerage has a Cash account and a Stock account
           *   In the Stock account, I have a Stock named Shorted Stock (Symbol: SHRT) - that I short
              *   Also in the Stock account is a Cash Set Aside account
                 *   When you short a stock, your broker will set aside money (from your brokerage Cash account) for the CURRENT value of the stock you shorted
                    *   This Cash Set Aside account works like a liability account for the fact that you have to eventually buy back the shares that you shorted
     *   In Equity I have only an Opening balance
     *   Note that I did not try to use an expense for commissions or an income account to track gain(loss) on when stock positions are closed

Here is a list of the share prices for SHRT

  *   [cid:image002.png at 01D75E3A.9EA64590]

Below is the information that I used to record my transactions:

  *   1/1       Opening Cash balance from Equity for $10,000
  *   1/1       Sold 100 shares of SHRT at $40 per share
        *   Note that when you first enter a "short sale" - with negative shares - a pop up shows - saying that the values of the transaction are inconsistent - I chose "Value (Changed)" to make it go thru
           *   [cid:image003.png at 01D75E3A.9EA64590]
  *   1/2       Price changed to $30
  *   1/3       Sold 100 share of SHRT at $30
  *   1/4       Price changed to $35
  *   1/5       Price changed to $24
  *   1/6       Buy back 75 shares at $24 per share
  *   1/7       Price changed to $10
  *   1/8       Buy back the remaining 125 shares at $10 per share

Transactions:
Account
Date
Description
Transfer from/to
Debit
Credit
Equity Opening Balance
1/1/2021
Opening Balance
Cash

$10,000.00

Account
Date
Description
Transfer from/to
Debit
Credit
Cash
1/1/2021
Opening Balance
Opening Balance
$10,000.00

Cash
1/1/2021
Sell Short 100 shares of SHRT at $40 per share
Shorted Stock
$4,000.00

Cash
1/1/2021
Cash Set Aside by Broker for CURRENT value of shorted stock (Broker must ensure that you have enough funds to cover the short position)
Cash Set Aside

$4,000.00
Cash
1/2/2021
Cash Set Aside is adjusted for current price of SHRT of $30/share (down from $40/share when purchased)
Cash Set Aside
$1,000.00

Cash
1/3/2021
Sell Short 100 shares of SHRT at $30 per share
Shorted Stock
$3,000.00

Cash
1/3/2021
Cash Set Aside is adjusted for latest short sale
Cash Set Aside

$3,000.00
Cash
1/4/2021
Cash Set Aside is adjusted for current price of SHRT of $35/share (up from $30/share)
Cash Set Aside

$1,000.00
Cash
1/5/2021
Cash Set Aside is adjusted for current price of SHRT of $24/share (down from $35/share)
Cash Set Aside
$2,200.00

Cash
1/6/2021
Buy back 75 share of SHRT at $24 per share
Shorted Stock

$1,800.00
Cash
1/6/2021
Cash Set aside is adjusted for Buy Back of 75 shares at $24/share
Cash Set Aside
$1,800.00

Cash
1/7/2021
Adjust for price change of $10 (down from $24)
Cash Set Aside
$1,750.00

Cash
1/8/2021
Buy back all remaining 125 shares at $10 per share
Shorted Stock

$1,250.00
Cash
1/8/2021
Adjust for buy back of all remaining 125 shares at $10/share
Cash Set Aside
$1,250.00

$25,000.00
$11,050.00

$3,950 profit from the transactions

$13,950.00

Account
Date
Description
Transfer from/to
Debit
Credit
Cash Set Aside
1/1/2021
Cash Set Aside by Broker for CURRENT value of shorted stock (Broker must ensure that you have enough funds to cover the short position)
Cash
$4,000.00

Cash Set Aside
1/2/2021
Cash Set Aside is adjusted for current price of SHRT of $30/share (down from $40/share when purchased)
Cash

$1,000.00
Cash Set Aside
1/3/2021
Cash Set Aside is adjusted for latest short sale
Cash
$3,000.00

Cash Set Aside
1/4/2021
Cash Set Aside is adjusted for current price of SHRT of $35/share (up from $30/share)
Cash
$1,000.00

Cash Set Aside
1/5/2021
Cash Set Aside is adjusted for current price of SHRT of $24/share (down from $35/share)
Cash

$2,200.00
Cash Set Aside
1/6/2021
Cash Set aside is adjusted for Buy Back of 75 shares at $24/share
Cash

$1,800.00
Cash Set Aside
1/7/2021
Adjust for price change of $10 (down from $24)
Cash

$1,750.00
Cash Set Aside
1/8/2021
Adjust for buy back of all remaining 125 shares at $10/share
Cash

$1,250.00

$8,000.00
$8,000.00

$0.00

Account
Date
Description
Transfer from/to
Debit
Credit
Shorted Stock
1/1/2021
Sell Short 100 shares of SHRT at $40 per share
Cash

100 SHRT
Shorted Stock
1/3/2021
Sell Short 50 shares of SHRT at $30 per share
Cash

100 SHRT
Shorted Stock
1/6/2021
Buy back 75 share of SHRT at $24 per share
Cash
75 SHRT

Shorted Stock
1/8/2021
Buy back all remaining 125 shares at $10 per share
Cash
125 SHRT

200 SHRT
200 SHRT

0 SHRT

=================================================================

Same Transactions as above - but these are screen shots for the various accounts:

[cid:image004.png at 01D75E3A.9EA64590]

[cid:image005.png at 01D75E3A.9EA64590]

[cid:image006.png at 01D75E3A.9EA64590]

[cid:image007.png at 01D75E3A.9EA64590]

-----Original Message-----
From: Geoff <cleanoutmyshed at gmail.com<mailto:cleanoutmyshed at gmail.com>>
Sent: Tuesday, June 8, 2021 8:27 PM
To: Jai Rao <jaibrao at outlook.com<mailto:jaibrao at outlook.com>>; gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org>
Subject: Re: Enable Online Stock Price Updating in GnuCash

Hi Jai

Well done.  To answer your next question:

> Is there a way to properly record a stock that has  > been sold short?

Gnucash will permit you to run a negative balance on a stock, see attached screenshot.

Note that I am not an accountant, but I think you should also book a future liability for when the short falls due.

Hope this helps.

Regards

Geoff

=====

On 9/06/2021 10:33 am, Jai Rao wrote:

> Chris & Geoff,

>

> Thanks for your help - I was able to get it to work

>

> 1. I went to

> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.a

> lphavantage.co%2Fsupport%2F%23api-key&data=04%7C01%7C%7Ce148c4add3

> bb480eb90608d92ae5a1c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C63

> 7587988031777834%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV

> 2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=1Xnn98XrrrAIlLr

> QCYfulQYD0mold%2BJq1940XL5csPE%3D&reserved=0

>

>        I got a free API Key

>

>           I went to GnuCash -> Edit -> Preferences -> Online Quotes ->

> and input the Alpha Vantage API Key

>

> 2. I re-ran the "Install Online Price Retrieval for GnuCash" - from

> the Start Menu

>

>           This time I let it run overnight - even though it looked

> like it was stalled after about 30 minutes

>

>                 In the morning it said that it succeeded

>

>                      I ran the CMD Prompt

>

>                           Went to the directory:  C:\Program Files

> (x86)\gnucash\bin

>

>                                  I typed in:   "perl gnc-fq-check

>

>                                       It showed several quote sites as

> being available

>

> I would recommend that someone update the GnuCash Tutorial to reflect

> the above (for Windows)

>

>   * Main part was probably to wait overnight for the "Install Online

>     Price Retrieval for GnuCash" to finish

>   * Also in the tutorial where it says to run "gnc-fq-update" in the

>     shell (cmd) prompt - it would be better to say run it by typing

>     "perl gnc-fq-update"

>

> Different question:  Is there a way to properly record a stock that

> has been sold short?

>

> Thanks,

>

> Jai Rao

>

> -----Original Message-----

> From: Chris Good <goodchris96 at gmail.com<mailto:goodchris96 at gmail.com>>

> Sent: Monday, June 7, 2021 8:57 PM

> To: gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org>

> Cc: jaibrao at outlook.com<mailto:jaibrao at outlook.com>

> Subject: RE: Enable Online Stock Price Updating in GnuCash

>

> Message: 11

>

> Date: Fri, 4 Jun 2021 04:37:09 +0000

>

> From: Jai Rao <jaibrao at outlook.com <mailto:jaibrao at outlook.com<mailto:jaibrao at outlook.com%20%3cmailto:jaibrao at outlook.com>>>

>

> To: "GnuCash-User at GnuCash.Org <mailto:GnuCash-User at GnuCash.Org><mailto:GnuCash-User at GnuCash.Org%20%3cmailto:GnuCash-User at GnuCash.Org%3e>"

> <GnuCash-User at GnuCash.Org <mailto:GnuCash-User at GnuCash.Org<mailto:GnuCash-User at GnuCash.Org%20%3cmailto:GnuCash-User at GnuCash.Org>>>

>

> Subject: [GNC] Enable Online Stock Price Updating in GnuCash

>

> Message-ID:

>

> <SN6PR07MB499156C5DD563583C14A2EDBA03B9 at SN6PR07MB4991.namprd07.prod.outlook.

>

> com>

>

> Content-Type: text/plain; charset="us-ascii"

>

> Sir / Madam,

>

> I am trying to enable Online Stock Price Updating in GnuCash - (in

> Windows 10).

>

>    *   I have installed Strawberry Perl

>

>       *   Then I ran the "Install Online Price Retrieval for GnuCash"

> - from

>

> the Start Menu

>

>          *   This goes thru a long process in the CMD Prompt (Shell)

> window

>

>             *   It seems to make it to the end - see picture below:

>

>             *   [cid:6644e8d2-a3a2-4004-9571-53b898113207]

>

>             *   Then I open a new CMD Prompt and type:  "perldoc

>

> Finance::Quote"

>

>                *   It says that there is no documentation for this

>

>    *   I then tried to go to the GnuCash Wiki page - but I was not

> able to

>

> get anyhere.

>

> I also could not figure out how to do the Root Shell to run "gnc-fq-update"

>

> Can you please let me know what to do?

>

> Thanks,

>

> Jai Rao

>

> -------------- next part --------------

>

> A non-text attachment was scrubbed...

>

> Name: image.png

>

> Type: image/png

>

> Size: 78003 bytes

>

> Desc: image.png

>

> URL:

>

> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Flists

> .gnucash.org%2Fpipermail%2Fgnucash-user%2Fattachments%2F20210604%2Fc35

> 8e8&data=04%7C01%7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe

> 9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637587988031777834%7CUnknown%7CTWFpb

> GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0

> %3D%7C1000&sdata=NhKmpgzLyh%2FGKCkw6ViRLtHy9aV9ZLHmXOZd6LjaUw4%3D&

> amp;reserved=0

>

> 8c/attachment.png>

>

> -------------- next part --------------

>

> A non-text attachment was scrubbed...

>

> Name: Outlook-q35xlm5e.png

>

> Type: image/png

>

> Size: 68180 bytes

>

> Desc: Outlook-q35xlm5e.png

>

> URL:

>

> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Flists

> .gnucash.org%2Fpipermail%2Fgnucash-user%2Fattachments%2F20210604%2Fc35

> 8e8&data=04%7C01%7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe

> 9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637587988031777834%7CUnknown%7CTWFpb

> GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0

> %3D%7C1000&sdata=NhKmpgzLyh%2FGKCkw6ViRLtHy9aV9ZLHmXOZd6LjaUw4%3D&

> amp;reserved=0

>

> 8c/attachment-0001.png>

>

> -------------- next part --------------

>

> A non-text attachment was scrubbed...

>

> Name: Outlook-udrywjix.png

>

> Type: image/png

>

> Size: 68180 bytes

>

> Desc: Outlook-udrywjix.png

>

> URL:

>

> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Flists

> .gnucash.org%2Fpipermail%2Fgnucash-user%2Fattachments%2F20210604%2Fc35

> 8e8&data=04%7C01%7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe

> 9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637587988031777834%7CUnknown%7CTWFpb

> GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0

> %3D%7C1000&sdata=NhKmpgzLyh%2FGKCkw6ViRLtHy9aV9ZLHmXOZd6LjaUw4%3D&

> amp;reserved=0

>

> 8c/attachment-0002.png>

>

> -------------- next part --------------

>

> A non-text attachment was scrubbed...

>

> Name: Outlook-0cdyg2x0.png

>

> Type: image/png

>

> Size: 68180 bytes

>

> Desc: Outlook-0cdyg2x0.png

>

> URL:

>

> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Flists

> .gnucash.org%2Fpipermail%2Fgnucash-user%2Fattachments%2F20210604%2Fc35

> 8e8&data=04%7C01%7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe

> 9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637587988031787472%7CUnknown%7CTWFpb

> GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0

> %3D%7C1000&sdata=95DMlTbMe%2BZXC%2F5S%2BmuTOSi9iZIV6hckZhWgSfIijLQ

> %3D&reserved=0

>

> 8c/attachment-0003.png>

>

> ------------------------------

>

> Hi Jai Rao,

>

> I don't think you need to run qnc-fq-update with Administrative

> privileges (the Windows equivalent of Linux root), but to do so:

>

> Click in the Cortana Ask me anything field to the right of the Start

> button, begin typing "cmd", a search menu should appear. The menu

> should display "Command Prompt". Right click on "Command Prompt", and

> select "Run as administrator".

>

> I suggest you use the instructions in

>

> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.

> gnucash.org%2Fwiki%2FOnline_Quotes%23Helper_Scripts&data=04%7C01%7

> C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe9f640afb435aaaaaaaaaaa

> a%7C1%7C0%7C637587988031787472%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjA

> wMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=L

> IPtGsoowQ%2Bx0C4FYXQHcrTXLjqoqcKJVfoJyRh1Hpg%3D&reserved=0

> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki

> .gnucash.org%2Fwiki%2FOnline_Quotes%23Helper_Scripts&data=04%7C01%

> 7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe9f640afb435aaaaaaaaaa

> aa%7C1%7C0%7C637587988031787472%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLj

> AwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=

> LIPtGsoowQ%2Bx0C4FYXQHcrTXLjqoqcKJVfoJyRh1Hpg%3D&reserved=0>

> to run gnc-fq-check to see if there is a problem with your

> Finance::Quote installation, and it that doesn't find an error, use

> gnc-fq-dump to test, like say:

>

> perl gnc-fq-dump alphavantage IBM

>

> (There was a typo in

>

> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.

> gnucash.org%2Fwiki%2FOnline_Quotes%23Source_Alphavantage.2C_US&dat

> a=04%7C01%7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe9f640afb435

> aaaaaaaaaaaa%7C1%7C0%7C637587988031787472%7CUnknown%7CTWFpbGZsb3d8eyJW

> IjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&

> amp;sdata=ApJUjsjqkPDH%2B4YubtnhSPcqGcwjjVUWGHvhVdQCQFk%3D&reserve

> d=0

> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki

> .gnucash.org%2Fwiki%2FOnline_Quotes%23Source_Alphavantage.2C_US&da

> ta=04%7C01%7C%7Ce148c4add3bb480eb90608d92ae5a1c5%7C84df9e7fe9f640afb43

> 5aaaaaaaaaaaa%7C1%7C0%7C637587988031787472%7CUnknown%7CTWFpbGZsb3d8eyJ

> WIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000

> &sdata=ApJUjsjqkPDH%2B4YubtnhSPcqGcwjjVUWGHvhVdQCQFk%3D&reserv

> ed=0> that made it difficult to see the Windows alphavantage

> instructions on that page which I have now fixed.)

>

> If you find a problem, we should be able to help you fix it.

>

> Regards, Chris Good

>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 91451 bytes
Desc: image001.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 3895 bytes
Desc: image002.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 130182 bytes
Desc: image003.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 4329 bytes
Desc: image004.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image005.png
Type: image/png
Size: 45527 bytes
Desc: image005.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.png
Type: image/png
Size: 29752 bytes
Desc: image006.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0012.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image007.png
Type: image/png
Size: 15459 bytes
Desc: image007.png
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0013.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Short.gnucash
Type: application/octet-stream
Size: 3752 bytes
Desc: Short.gnucash
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20210611/6fcb07ae/attachment-0001.obj>