Search code examples
python-3.xpygsheets

Python: Exporting Output to Google Sheets

I wrote this web scraping program that extracts retail trading sentiment data for 26 forex pairs from IG markets.

The output in the console looks like:

AUD-CAD: 64% of client accounts are short.
AUD-CHF: 54% of client accounts are long.
AUD-JPY: 60% of client accounts are short.
AUD-NZD: 60% of client accounts are long.
AUD-USD: 53% of client accounts are short.
CAD-CHF: 56% of client accounts are long.
CAD-JPY: 56% of client accounts are long.
CHF-JPY: 68% of client accounts are short.
EUR-AUD: 68% of client accounts are long.
EUR-CAD: 65% of client accounts are short.
EUR-CHF: 66% of client accounts are long.
EUR-GBP: 53% of client accounts are short.
EUR-JPY: 57% of client accounts are short.
EUR-NZD: 55% of client accounts are long.
EUR-USD: 54% of client accounts are short.
GBP-AUD: 73% of client accounts are long.
GBP-CAD: 66% of client accounts are long.
GBP-CHF: 63% of client accounts are long.
GBP-JPY: 52% of client accounts are short.
GBP-NZD: 57% of client accounts are long.
GBP-USD: 59% of client accounts are short.
SPOT-FX-NZDCAD: 68% of client accounts are short.
NZD-CHF: 59% of client accounts are short.
NZD-JPY: 57% of client accounts are short.
NZD-USD: 72% of client accounts are short.
USD-CAD: 69% of client accounts are long.
USD-CHF: 79% of client accounts are long.
USD-JPY: 58% of client accounts are long.

I would like to export this data to a Google Sheet named "GsheetTest", but I'm stuck and I have no idea how to do it.

Google API is enabled. I've created the credentials, got the service account json key.

I'm able to write simple text to this google sheet file "GsheetTest" using pygsheets and panda dataframe.

Here's the code:

import bs4, requests

def getIGsentiment(pairUrl):
    res = requests.get(pairUrl)
    res.raise_for_status()'

soup = bs4.BeautifulSoup(res.text, 'html.parser')
elems = soup.select('.price-ticket__sentiment')
return elems[0].get_text(" ", strip = True)


pair_list = ['aud-cad', 'aud-chf', 'aud-jpy', 'aud-nzd', 'aud-usd', 'cad-chf', 'cad-jpy', 
             'chf-jpy', 'eur-aud', 'eur-cad', 'eur-chf', 'eur-gbp', 'eur-jpy', 'eur-nzd', 
             'eur-usd', 'gbp-aud', 'gbp-cad', 'gbp-chf', 'gbp-jpy', 'gbp-nzd', 'gbp-usd', 
             'spot-fx-nzdcad', 'nzd-chf', 'nzd-jpy','nzd-usd', 'usd-cad', 'usd-chf',
             'usd-jpy']
for i in range(len(pair_list)):
    retail_positions = getIGsentiment('https://www.ig.com/us/forex/markets-forex/ +(pair_list[i]))
    pair = pair_list[i]
    print(pair.upper() +': ' + retail_positions[0:32].rstrip() + '.')
Solution

  • First open json file in browser and find email address, now share spreadsheet with that email along with editing right, after that use following code..

    #Import these libraries or pip install if not installed already
from oauth2client.service_account import ServiceAccountCredentials
import gspread
import bs4, requests
def getIGsentiment(pairUrl):
    res = requests.get(pairUrl)
    res.raise_for_status()'

soup = bs4.BeautifulSoup(res.text, 'html.parser')
elems = soup.select('.price-ticket__sentiment')
return elems[0].get_text(" ", strip = True)


pair_list = ['aud-cad', 'aud-chf', 'aud-jpy', 'aud-nzd', 'aud-usd', 'cad-chf', 'cad-jpy', 
             'chf-jpy', 'eur-aud', 'eur-cad', 'eur-chf', 'eur-gbp', 'eur-jpy', 'eur-nzd', 
             'eur-usd', 'gbp-aud', 'gbp-cad', 'gbp-chf', 'gbp-jpy', 'gbp-nzd', 'gbp-usd', 
             'spot-fx-nzdcad', 'nzd-chf', 'nzd-jpy','nzd-usd', 'usd-cad', 'usd-chf',
             'usd-jpy']


#now you need auth and set scope 

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('path-to-your-json.json', scope)
#Auth
gc = gspread.authorize(creds)
sh = gc.open('Spread Sheet name You want to open')
worksheet = sh.add_worksheet('sheet to be added name', int(rows), int(columns))


for i in range(len(pair_list)):
    retail_positions = getIGsentiment('https://www.ig.com/us/forex/markets-forex/ +(pair_list[i]))
    pair = pair_list[i]
    foo = pair.upper() +': ' + retail_positions[0:32].rstrip() + '.'
    worksheet.insert_row(foo, 1)