Published using Google Docs
tips google news initiative
Updated automatically every 5 minutes


*-Thisdocument’s shortlink: http://bit.ly/googlefinancetools

----------

Reference videos

Google Finance training video: https://www.youtube.com/watch?v=d9LQsweHy20

Investigating companies/charities training video: https://www.youtube.com/watch?v=s06ClFa1CLM

Consumer retail tools training video: https://www.youtube.com/watch?v=6EMwKIibWgE

Visualizing business data in Flourish training video:
(1) Journalist's Toolbox: Business Tools, Pt. 3: Visualizing Data in Flourish - YouTube

—-----------

Part I: Scraping Data from Google Finance

Overview

Open these three pages:

1.Google Finance: Google Finance - Stock Market Prices, Real-time Quotes & Business News

2.Our practice spreadsheet, which you must make a copy of: GOOGLE FINANCE-SCRAPING 

3.Quick list of Google Finance scraping formulas: https://support.google.com/docs/answer/3093281?hl=en

EXERCISES

  1. How to pull stock data into a spreadsheet:

        Go to our spreadsheet: GOOGLE FINANCE-SCRAPING

                

In cell b2, type: )=googlefinance(A2,b1 and hit return for the price


In cell c2, type: =googlefinance(A2,c1) and hit return for the price

earnings ratio


In cell d2, type:  =googlefinance(A2,d1) and hit return for the 52-week high price

Write your own script for a 52-week low column (E):

  1. How to scrape historical stock prices:


Paste or type: =GOOGLEFINANCE(a2, "price","08/01/2020", today(), "daily")  in cell K1 to get daily ending prices since August 2020

GOO


Paste or type: =GOOGLEFINANCE(a3, "price","08/01/2020", today(), "weekly")  in cell L1 to get end-of-week Verizon prices since August 2020

 =GOOGLEFINANCE(a2, "price","05/06/2020", today(), "weekly")

Paste or type: =GOOGLEFINANCE("SSE", "price", DATE(2021,1,1), DATE(2022,05,06), "Weekly") in cell N1 to get end-of-week prices for the SSE since January 2021

Paste or type: =GOOGLEFINANCE("HSI", "price", DATE(2022,1,1), DATE(2022,07,26), "daily") in cell N1 to get end-of-day prices for the HSI since January 2022

Last 60 days of Tesla prices, paste or type:

​​=GOOGLEFINANCE("NASDAQ:TSLA","price",TODAY()-60,TODAY())

These functions will auto update over time so you’ll see current prices. It’s similar to how data scraping works.

  1. Focusing a sheet onto one stock

Set up a sheet to look like this, then in cell B2 under Value, paste this and hit return:

=GOOGLEFINANCE("NASDAQ:GOOG",A2)

=GOOGLEFINANCE("HOKCY",A2)

=GOOGLEFINANCE("AAPL",A2)

=GOOGLEFINANCE("NYSE:f",A2)

=GOOGLEFINANCE("NYSE:vz",A2)

=GOOGLEFINANCE("XOM",A2)

=GOOGLEFINANCE("f",A2)

Then grab the blue square in the lower right of the cell and drag down: It will populate the sheet with data from Apple stock and it’ll update as the market changes.Kristine Feldmann

  1. Formulas: To see any of the formulas, click on the blue ? button on the cell when you type in =GOOGLEFINANCE. You also can see them when you hit return after typing in Googlefinance and you can find many of them listed here: https://support.google.com/docs/answer/3093281?hl=en

  1. Practice scraping some stocks with this formula:

    =GOOGLEFINANCE("STOCK SYMBOL",a1)

  1. How to find stock ticker symbols
  1. Type in the stock name into Google Finance and the symbol pops up.
  2. Search the company name, exchange and the words stock symbol on Google
  3. Go to the stock exchange’s site and look for the database of them. Examples:

Stock Analysis: List of All Stock Ticker Symbols

NASDAQ Stock Screener: Database of Ticker Symbols

NYSE Listings Directory

*-Find more resources on the Journalist’s Toolbox stock market page in the Business vertical

  1. Fun story idea: How many times is AI or “artificial intelligence” mentioned in an earnings call? Reuters took a look.

  1. Scrape the FDIC Failed Banks List (or any other site) with this basic scraping formula:

=IMPORTHTML("https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/","table",0)

Site: https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/

Other sites to scrape (use blank formulas below):

TEST: https://www.bls.gov/news.release/cpi.t02.htm
(CPI data from the Bureau of Labor Statistics)

https://www.geostat.ge/en/modules/categories/195/business-statistics
(Georgia biz data)

https://www.inmo.ie/Trolley_Ward_Watch

https://www.transtats.bts.gov/AverageFare/

https://www.bls.gov/news.release/cpi.t02.htm

https://violationtracker.goodjobsfirst.org/?company_op=starts&company=&offense_group=&agency_code=EPA

=IMPORTHTML("URL","table",0)

=IMPORTHTML("https://www.geostat.ge/en/modules/categories/195/business-statistics","table",0)

=IMPORTHTML("URL","table",0)

=IMPORTHTML("URL","table",0)

=IMPORTHTML("URL","table",0)

=IMPORTHTML("URL","table",0)

For more data scraping exercises, go here: http://bit.ly/spjdatascrape 

Data scraping training video: https://www.youtube.com/watch?v=vIKwsQPvdHA&feature=youtu.be

------------

Part II: Backgrounding Companies and Charities

Journalist’s Toolbox: Investigating Companies: https://www.journaliststoolbox.org/2022/03/22/proxiesbackgrounding_companies/

Fun Hacks: Shortcuts to set up documents
Type one of these into your browser window:

         Sheets.new

Docs.new

Google Public Data Explorer: https://www.google.com/publicdata/directory

Good for quick charts on unemployment, and other finance charts.

Dataset search tool: https://datasetsearch.research.google.com/

Search: GDP France  … or a local/regional search

USAFacts.org: https://usafacts.org/
Non-partisan, non-profit org founded by ex-Microsoft CEO Steve Ballmer. Find clean datasets under the Economic vertical.

Visualping.io: https://visualping.io/
Track updates to websites.

—


SEC EDGAR database: EDGAR Company Filings

EDGAR lets you look at quarterly and annual filings for publicly-traded companies. I also houses “Form D” documents on private start-ups and venture capital firms, which also must report finances but not in much detail. FormDs.com does a good job of tracking those.

You can download PDFs of the quarterly and annual reports from public companies and ask AI tools to analyze them. Or simply read them yourself.

Another way:

Set up a free account at Fintool: https://www.fintool.com/

Write prompts like this one (stock symbol first provides best results):

$AAPL - How did Apple perform following the launch of Vision Pro?

You’ll get a robust result, but be sure to rewrite it in your own words

—---


Violation Tracker
https://www.goodjobsfirst.org/violation-tracker
The first wide-ranging database on corporate misconduct. Produced by the Corporate Research Project of Good Jobs First, it covers banking, consumer protection, false claims, environmental, wage and hour, safety, discrimination, price-fixing, and other cases resolved by federal regulatory agencies and all parts of the Justice Department since 2000 — plus cases from state AGs and selected state regulatory agencies.

World Bank: Projects by Country

https://projects.worldbank.org/en/projects-operations/project-country?lang=en&page=
Look up what countries are spending on COVID-19, development projects and more. Click on the “documents” and “procurement” tabs once you’ve selected the country and download the PDFs. GIJN has a little training video on how to use it here: https://www.youtube.com/watch?v=eU_heixnBMM

Fact-checking: Debunk those “faux experts” in business, medical, science and other reporting:

Dollars for Docs: https://projects.propublica.org/docdollars/

Dollars for Profs: https://projects.propublica.org/dollars-for-profs

Visualping.io: https://visualping.io/

Track when a website is updated

—---------
Investigating Private Companies and Startups

Journalist’s Toolbox: Investigating Companies (Public and Private)
https://www.journaliststoolbox.org/2021/12/10/proxiesbackgrounding_companies/

FormDs.com

https://www.formds.com/

Track investments into startups, growing businesses, hedge funds, and private equity firms. They do this by sorting, filtering and extracting critical information from the hundreds of SEC filings (called "Form Ds") filed each day. The site processes SEC filings on a real-time basis and often learns about new investments before newspapers and online publications. You can search for people, company names, specific industries, or focus on regions or cities.

—------


Part III: Investigating Non-Profits

Journalist’s Toolbox: Charities and Form 990s

​​https://www.journaliststoolbox.org/2022/04/13/form_990scharities/

Guidestar: https://www.guidestar.org/

*-Candid 990 Finder: https://candid.org/research-and-verify-nonprofits/990-finder
The Foundation Center and GuideStar combine their data so journalists can find the most recent Forms 990s from charities and foundations. Free work-around for Guidestar

ProPublica Nonprofit Explorer: https://projects.propublica.org/nonprofits/
Search by charity/foundation, state, type of charity or mission/focus.

CharityWatch: Rankings, highest-paid execs, etc.: https://www.charitywatch.org/

Charity Watch: Resources for Donors and Journalists
https://www.charitywatch.org/resources-for-donors

Charity Watch: State Charity Regulators
https://www.charitywatch.org/resources-for-donors/state-charity-regulators

Find who is regulating charities in your state as it varies by government agency. Includes contact information. Also available at the National Association of State Charity Officials https://www.nasconet.org/resources/state-government/

Violation Tracker
https://www.goodjobsfirst.org/violation-tracker
The first wide-ranging database on corporate misconduct. Produced by the Corporate Research Project of Good Jobs First, it covers banking, consumer protection, false claims, environmental, wage and hour, safety, discrimination, price-fixing, and other cases resolved by federal regulatory agencies and all parts of the Justice Department since 2000 — plus cases from state AGs and selected state regulatory agencies.

IRS Tax-Exempt Organization Search
https://apps.irs.gov/app/eos/

National Center for Charitable Statistics
https://nccs.urban.org/
Part of the Urban Institute, this site has some older data on charitable giving.

Charity Intelligence Canada
https://www.charityintelligence.ca/index.php
Similar to the US’s Charity Watch.

The Journalist’s Resource: Where to Find Public Records, Data on Charities
https://journalistsresource.org/home/investigating-nonprofits-and-charities-where-to-find-internal-data-and-public-records/

The Journalist’s Resource: How to Read a Form 990
https://live-journalists-resource.pantheonsite.io/politics-and-government/think-tanks-writing-research-journalists/

ProPublica PPP Loan Tracker

https://projects.propublica.org/coronavirus/bailouts/

Has details on all 11.5 million approved PPP loans, including how much of each loan has been reported as forgiven. Search by organization name, type, lender or even ZIP code.

VisualPing.io
https://visualping.io/

Track updates on websites

—----

Part IV: Consumer Retail and Inflation

Journalist’s Toolbox Tools

https://www.journaliststoolbox.org/2022/04/09/consumer_retail_inflation/


Inflation Tools
The Consumer Price Index impacts many things: rent, cars, food, utilities, Social Security increases, child support, etc.

Bureau of Labor Statistics: Consumer Price Index Home Page
https://www.bls.gov/cpi/

BLS: CPI Inflation Calculator
https://data.bls.gov/cgi-bin/cpicalc.pl

Try $4.08 in July 2008. What is it as of last month?


BLS: CPI Average Price Data, U.S. City Average (AP)
https://data.bls.gov/PDQWeb/ap
A one-screen data search where you can look up product average prices by US cities overall, regions, etc. Awesome database.

 

West Egg Inflation Calculator
https://westegg.com/inflation/
Good for fact-checking the BLS CPI calculators.

dMarie Time Capsule Almanac
http://www.dmarie.com/timecap/
Almanac that lets you type in a date to get product prices and pop culture information from that date. (Average price of bread, gas, car, home, etc.)

Google Public Data Explorer
https://www.google.com/publicdata/directory
Create quick CPI graphics using BLS data. Use IMF, World Bank and other data for more business graphics

Google Dataset Search
https://datasetsearch.research.google.com/

                
        Video: Dataset Search

        Try searching:

        Inflation

US footwear consumer price index

        Retail price breakdown of Nike Air Jordan shoes 2015

Google Trends: https://trends.google.com/trends/

        
        Video: Google Trends

        Google Trends e-newsletter: http://goo.gle/trendsemail

        Google Trends Newsletter Example (Mike access only): https://mail.google.com/mail/u/0/#search/simon+rogers/FMfcgzGqQSTcnLnprJfjnCCGCpfXdFlX

*-When attributing Google Trends results, use: according to an analysis of Google search data …

—----------

Price-Tracking Tools

Keepa: Tracks Amazon product price history

https://keepa.com/#!

Free to use but $189 annual/$19 monthly subscription gets more data access (daily quota)

Search for some products in Keepa for practice:

32-inch 3-series Roku TV

Adidas men’s Adilette shower slides

Sennheiser HD 25 SE monitor headphones

Blink outdoor camera

Another free option: CamelCamelCamel: https://camelcamelcamel.com/

Google Shopping: https://shopping.google.com/

        * Free results since April 2020

Search for:

        ​​Ashley Tallenger Counter Height Bar Stool

        Samsung 32-inch TV

—-------

Articles on Covering Inflation | Examples

NY Times: Rising Halloween Candy Prices

https://www.nytimes.com/2023/10/27/business/halloween-candy-price-sugar-chocolate.html

Columbia Journalism Review: How Not to Report on Inflation
https://archives.cjr.org/the_audit/how_not_to_report_on_inflation.php

Poynter: What Journalists Should Look for in Inflation Figures
https://www.poynter.org/reporting-editing/2022/what-journalists-should-look-for-in-tomorrows-inflation-figures/

The Journalist’s Resource: People Trust Inflation Forecasts from the Fed More Than Traditional News Stories

https://journalistsresource.org/economics/inflation-forecasts-federal-reserve-news-media/

National Federation of Independent Business
https://www.nfib.com/
Covers small business needs and has studies on prices and inflation.

—------

More search shortcuts:

        *Search within a site for a topic: site:amazon.com Blink Outdoor Camera

        *Search for a file format/topic: filetype:xlsx US footwear consumer price index

                Site:bls.gov consumer price index

filetype:xlsx gdp by country

"gdp" AROUND(20) "Ireland"

*Search for a price: ​​camera $400

*Price range: camera $500..$800

Video: Search tips

Full list of search shortcuts/operators: https://support.google.com/websearch/answer/2466433?hl=en

Also:

Google Advanced search: https://www.google.com/advanced_search?hl=en&fg=1

Other Tools and Resources

Lustre

https://lustre.ai/

A free, Webby-award-winning product search engine that automatically compares products, prices, and gathers trustworthy reviews in one place. The Chrome extension works on Amazon, so you can see independent product reviews, rather than just random people’s comments. Handy on Black Friday or anytime you’re overwhelmed with options.

FakeSpot.com
https://www.fakespot.com/
Spot and avoid shady sellers, products with dishonest reviews and scam websites for free with Fakespot for Chrome, Firefox, iOS and Android. Uses AI technology.

FDA: Recalls, Market Withdrawals, & Safety Alerts

https://www.fda.gov/safety/recalls-market-withdrawals-safety-alerts
Drugs and food (including pet food) recalled for health and other reasons. Search or download data since mid-2019

FDA: Manufacturer and User Facility Device Experience Database (MAUDE)

https://www.fda.gov/medical-devices/mandatory-reporting-requirements-manufacturers-importers-and-device-user-facilities/manufacturer-and-user-facility-device-experience-database-maude
Reports of adverse events involving medical devices (defibrillators, pacemakers, ventilators, etc.)

MedWatch

https://www.fda.gov/safety/medwatch-fda-safety-information-and-adverse-event-reporting-program
Safety alerts about medicines, biologics (blood), medical devices, cosmetics, food, etc.

Consumer Complaint Database
https://www.consumerfinance.gov/data-research/consumer-complaints/
Complaints about credit cards, banks, loans, debt collectors. State and ZIP code of the complainant are listed but not the name.

International Monetary Fund: World Economic Outlook Data

https://www.imf.org/en/Data
A huge database of easily downloadable data on global financial stability

Statista: Global Business and Data Platform
https://www.statista.com/
This paid site offers financial and COVID-19  insights and facts from more than 170 industries around the world.

World Bank Open Data
https://data.worldbank.org/

—-------

Part V: Google Flourish Graphic

Set up a free account on Google Flourish: https://flourish.studio/

Then follow the directions below to build a chart on top tech company 2019 employment by race (a grouped bar chart)

1.Open the Top Tech Company Racial Breakdown data file in this folder and download it to your desktop:

https://drive.google.com/drive/folders/0B4AbFFVbzuMDbVlNNnhNMFJQaXM

2.In Flourish, click on the red “NEW” button.

3.Select the Column chart (grouped) in the first group of graphic options.

4.Make sure you select the “DATA” tab at the top of the interface.

5.Hit the “Import Data” button in the upper right, select the spreadsheet you downloaded. If it asks you to make the data public or private, select “public.” You would select private if you were working on a sensitive topic that you could make public later when you’re ready to publish.

6. Make sure all of the columns are highlighted in pink or purple.

7. Click on the Preview tab to see what the chart looks like

8. In the right column, select the HEADER menu and for Title: type/paste in: Diversity breakdown of employees at top tech companies

9..Now go down to the FOOTER menu on the right side of the toolbar. Under source name, paste this: Compiled by company reports

11. In the Notes section of the photo, add Graphic: YOUR NAME. Groupon and Instagram figures are from May 2018 and reported in “Diversity in Tech,” by David McCandless • Other figures were taken from company reports in 2019 and compiled by Evan F. Moore/Sun-Times

12. Hit the Export and Publish button in the upper right and follow the -==directions.

* Here’s what the graphic should look like when you’re finished (scroll down):

https://chicago.suntimes.com/2020/2/17/21076129/shelton-banks-google-facebook-twitter-re-work-training-tech-diversity

—-------

On Your Own: Build this Graphic in Flourish

Top tech company 2019 employment by gender (column chart)

1. Open the Top Tech Company Gender data file in this folder and download it to your desktop:

https://drive.google.com/drive/folders/0B4AbFFVbzuMDbVlNNnhNMFJQaXM

2.In Flourish, click on the red “NEW” button.

3.Select the Column chart in the first group of graphic options.

4.Make sure you select the “DATA” tab at the top of the interface.

5.Hit the “Import Data” button in the upper right, select the spreadsheet you downloaded. If it asks you to make the data public or private, select “public.” You would select private if you were working on a sensitive topic that you could make public later when you’re ready to publish.

6. Make sure all of the columns are highlighted in pink or purple.

7. Click on the Preview tab to see what the chart looks like

8. In the right column, select the HEADER menu and for Title: type/paste in: Gender breakdown of employees at top tech companies

9..Now go down to the FOOTER menu on the right side of the toolbar. Under source name, paste this: Compiled by company reports

11. In the Notes section of the photo, add Graphic: YOUR NAME. Data compiled by Evan F. Moore/Chicago Sun-Times

12. Hit the Export and Publish button in the upper right and follow the directions.

* Here’s what the graphic should look like when you’re finished (scroll down):

https://chicago.suntimes.com/2020/2/17/21076129/shelton-banks-google-facebook-twitter-re-work-training-tech-diversity

—---